DSXchange

Hi everybody

How does an OCI stage find an Oracle Instance? I have this problem. I'm checking a DataStage Server Job, release 7.5.2, loadind data from a flat file to an Oracle Table using a Bulk Stage. It doesn't work, obviously, and the error displayed in the log is:

*** Output from command was: ***

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Sep 1 15:43:48 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL*Loader-951: Error calling once/load initialization
ORA-00942: table or view does not exist


I think that this problems is releted to the environment configuration because if I connect to Oracle Instance using TOAD, or SQL Navigator I can see and reference the table. So the question is

How does a DataStage Job find the information to connect to an Oracle Instance? If I want to connect to an Oracle Instace from SQLNavigator or TOAD or SQLPlus the system finds the Instance's information reading the tnsnames.ora file configurations. Is it the same path used by DataStage or not?

You need to add ORACLE_HOME and the other variables to dsenv. Do a search this has been covered in great detail. You may need to start and stop DataStage.

kduke wrote:You need to add ORACLE_HOME and the other variables to dsenv. Do a search this has been covered in great detail. You may need to start and stop DataStage.

Thank you for your answer. I suppose to be able to set only one Oracle environment, so DataStage Server would have to reference the same Oracle's instance I see using SQLNavigator. Why does the Job return me the error segnaled in my previous message? I can't access the Server machine (MsWindows 2003) as administrator so I can't use any tools to check DataStage configuration.....

It works the same way that it does in TOAD. Typically, you point it to an ORACLE_HOME and it uses the tnsnames.ora file there. Obviously, there can be more to it than that but that's normally how it works.

The fact that you got a Table or View does not exist error shows you are connected to an instance. You need to verify that you've connected to the correct one, that you've correctly specified the table's owner / schema in the stage and that you have grants to the table.

chulett wrote:It works the same way that it does in TOAD. Typically, you point it to an ORACLE_HOME and it uses the tnsnames.ora file there. Obviously, there can be more to it than that but that's normally how it works.

The fact that you got a Table or View does not exist error shows you are connected to an instance. You need to verify that you've connected to the correct one, that you've correctly specified the table's owner / schema in the stage and that you have grants to the table.

I have only to check to be connected to the correct instance at this point. The table name/schema specified in the stage is correct and the user have all type of grants.

Thank you for your help. Have a nice day.

Umberto

chulett wrote:It works the same way that it does in TOAD. Typically, you point it to an ORACLE_HOME and it uses the tnsnames.ora file there. Obviously, there can be more to it than that but that's normally how it works.

The fact that you got a Table or View does not exist error shows you are connected to an instance. You need to verify that you've connected to the correct one, that you've correctly specified the table's owner / schema in the stage and that you have grants to the table.

In any case, every Stage used to connect to an Oracle's instance, as for example OCI or Bulk Stage, use the same path specified in the dsn_env? Why in this case the Job using an OCI stage works and the Job using the bulk doesn't work? They are in the same project.....

Sure, they all use the $ORACLE_HOME specified in the dsenv file when they need to connect, but the Bulk Loader is a little different animal. It creates a 'control' file to set Schema and Table names post-connect which is probably your issue here.

Check the '.ctl' file the stage generates and make sure everything is correct there, especially the owner of the table being bulk loaded. If unsure, post it here. Or the .log file.

chulett wrote:Sure, they all use the $ORACLE_HOME specified in the dsenv file when they need to connect, but the Bulk Loader is a little different animal. It creates a 'control' file to set Schema and Table names post-connect which is probably your issue here.

Check the '.ctl' file the stage generates and make sure everything is correct there, especially the owner of the table being bulk loaded. If unsure, post it here. Or the .log file.

I just checked that and it is all ok. I'm not able to use administrator tool so I have to wait for the Service desk completed my Service Requeste. Any way thank you vary much for your advice.

Have a nice day

Umberto