Error while trying to retrieve text for error ORA-12154

gup · Post by **gup** » Thu Aug 17, 2006 2:16 pm

Hello All

In reference to searches I have done, one thread from last year sounds like the exact same problem I am having. I configured some Oracle sources, pulled in Tables from those sources, can isssue ./bin/dssh to connect to sources and run successful SQL commands.

When I go into Designer , and setup the Oracle stage and try to 'View Data' - I get the dreaded 12154 error.

How does the DataStage engine read or reference this file? I modified the TNSNAMES.ORA file to reflect my changes, I reset the engine . ./dsenv, Oracle home directories and paths are correct.

I am at a loss here - Why does the View Data not work in Designer when command driven works fine ? Does Datastage use a seperate Oracle driver when doing the View Data call? Any Help is appreciated!!

Matt

DSguru2B · Post by **DSguru2B** » Thu Aug 17, 2006 2:20 pm

Try to google the keyword ORA-12154. Try to lookinto it. Try this link for a start
http://www.is.mcgill.ca/minerva/Help/ora12154.htm

gup · Post by **gup** » Thu Aug 17, 2006 2:29 pm

Link was not real useful cause it is Windows based? Do you know how the DS Engine references this file?

chulett · Post by **chulett** » Thu Aug 17, 2006 2:44 pm

You need to properly configure DataStage for Oracle, something that is documented in both the Plug-in Configuration Guide and the OCI documentation.

It involves editing the dsenv file to set $ORACLE_HOME and to include the proper libraries in your equivalent SHLIB_PATH. Once that is done you need to bounce the DataStage server (not just source the dsenv file) before you can use Oracle stages in jobs. This includes running them or viewing Oracle data from an OCI stage.

Nagin · Post by **Nagin** » Tue Feb 20, 2007 12:25 pm

chulett wrote:You need to properly configure DataStage for Oracle, something that is documented in both the Plug-in Configuration Guide and the OCI documentation.

It involves editing the dsenv file to set $ORACLE_HOME and to include the proper libraries in your equivalent SHLIB_PATH. Once that is done you need to bounce the DataStage server (not just source the dsenv file) before you can use Oracle stages in jobs. This includes running them or viewing Oracle data from an OCI stage.

I am going through the same problem, I configured the odbc.ini , uvodbc.config and dsenv files but still getting the same ORA-12154 error. The only thing I haven't done is the SHLIB_PATH, Is it something I have to add to the dsenv file or some where else? If it has to be added to the dsenv, I already have the following entry 'LIBPATH=$DSHOME/../branded_odbc/lib:$DSHOME/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/lib32:$DSHOME/uvdlls:$DSHOME/java/jre/bin/classic:$DSHOME/java/jre/bin;export LIBPATH'. Is that same as the SHLIB_PATH?

Any other thing needs to be checked or configured to be able to view data?

Thanks for the help.

chulett · Post by **chulett** » Tue Feb 20, 2007 12:39 pm

You are configuring this for ODBC or OCI? The first two files you mentioned are for ODBC while the third is needed for OCI.

LIBPATH = SHLIB_PATH on some operating systems, you set whichever is appropriate for yours. You also need to make sure ORACLE_HOME is set in the dsenv file as well, then stop and restart DataStage before any changes will be 'read in'.

Nagin · Post by **Nagin** » Wed Feb 21, 2007 10:41 am

chulett wrote:You are configuring this for ODBC or OCI? The first two files you mentioned are for ODBC while the third is needed for OCI.

LIBPATH = SHLIB_PATH on some operating systems, you set whichever is appropriate for yours. You also need to make sure ORACLE_HOME is set in the dsenv file as well, then stop and restart DataStage before any changes will be 'read in'.

I am configuring this for DRS stage.
My understanding is that if we are using DataDirect Oracle wire protocol we don't need to configure dsenv. Is that right? I already have dsenv configured anyways.

But the problem still exists, I am getting the ORA-12154 error.
I looked at the sample odbc.ini file in the branded_odbc directory and found out that Non wire protocol last but entry is ServerName=Oracle host and Wire protocol last but entry is SID=Oracle SID. Now, my confusion is I am using wire protocol and the TNSnames.ora file has an entry like the following

PSECRP6 =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = NSYRP41E)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = PSECRP6)))

If I am using a wire protocol and odbc.ini files last but entry is SID=Oracle SID. do I need to have SID instead of SERVICE_NAME in the TNS entry?

I tried to search on the difference between SID and SERVICE_NAME in TNSnames, turns out that SID is for older Oracle versions 8.1 and before, for newer versions it is SERVICE_NAME and they should be interchangeable. We have Oracle 10.1 here.

I am totally lost. Can somebody shed some light on this?

Nagin · Post by **Nagin** » Thu Feb 22, 2007 2:36 pm

The problem has been resolved. I am able to view data now. It was wrong TNSnames that was causing the problem.

I think I am wrong regading the dsenv file not being needed when we are using the Oracle DataDirect wire protocol.
I think no matter which protocol we are using we need dsenv file to let DSEngine know where to look for other applications like oracle client and their TNSnames etc.

Thanks you all so much for the help.

DSguru2B · Post by **DSguru2B** » Thu Feb 22, 2007 2:37 pm

Great, you can mark the post as "Resolved" then.

Nagin · Post by **Nagin** » Thu Feb 22, 2007 2:49 pm

DSguru2B wrote:Great, you can mark the post as "Resolved" then.

I guess I can't mark the post as resolved as I am not the one started it.

DSguru2B · Post by **DSguru2B** » Thu Feb 22, 2007 2:51 pm

I did'nt bother seeing who was the owner.
Thats why they say, never hijack a post. Sorry about that