Oracle Enterprise Stage Error

[rupeshg]

Hi

I have installed DS PX 7.5x2 on windows. Database is Oracle 11g.

I am trying to use Oracle Enterprise Stage to read data directly from oracle db and get the required rows in a dataset.

I get the following error, when I try to view data.

##I TFCN 000001 14:30:49(000) <main_program>
Ascential DataStage(tm) Enterprise Edition 7.5
Copyright (c) 2004, 1997-2004 Ascential Software Corporation.
All Rights Reserved


##I TOSH 000002 14:30:49(001) <main_program> orchgeneral: loaded
##I TOSH 000002 14:30:49(002) <main_program> orchsort: loaded
##I TOSH 000002 14:30:49(003) <main_program> orchstats: loaded
>##E TOSH 000205 14:30:49(004) <main_program> PATH search failure:
>##E TOSH 000000 14:30:49(005) <main_program> Error loading "orchoracle": Could not load "orchoracle": The specified module could not be found.
>##E TOSH 000000 14:30:49(006) <main_program> Could not locate operator definition, wrapper, or Unix command for "oraread"; please check that all needed libraries are preloaded, and check the PATH for the wrappers
>##E TCOS 000029 14:30:49(007) <main_program> Creation of step finished with status = FAILED

Read Method: Table
DB Options: Auto-Generated

If I replace the stage with ODBC stage, and give the same DB Options it works fine.

I went through the documentation, and realised that I have to set ORACLE_HOME and ORACLE_SID as user defined environment variables. Also, I have to specify PATH as ORACLE_HOME/bin directory. I did all that, but no luck.
Next was to give appropriate privileges to the db username, hence I followed the steps as mentioned in documentation and granted the privileges as well, but I still get the same problem.

When I try to run the job with Oracle Enterprise Stage following is the log I get in fatal error messages.

main_program: PATH search failure:

main_program: Error loading "orchoracle": Could not load "orchoracle": The specified module could not be found.
Could not locate operator definition, wrapper, or Unix command for "oraread"; please check that all needed libraries are preloaded, and check the PATH for the wrappers

main_program: Creation of step finished with status = FAILED
Contents of phantom output file =>
DataStage Job 11 Phantom 304

And the job is aborted.

I tried to search through the forum with similar issues, but could not find one. Could someone please tell me what specifically I have to do to get this working? Thanks in advance.

Regards
Rupesh

[chulett]

Also, I have to specify PATH as ORACLE_HOME/bin directory. I did all that, but no luck.

Where exactly did you set this? And you added this to your current PATH statement, yes? You'd either need to spell out the complete path or use the Windows substitution characters for that to work correctly:

Code: Select all

%ORACLE_HOME%/bin

[rupeshg]

Hi Craig

I have specified the PATH in two places, as I was not sure where to add.

1. In Datastage Administration, Project Properties, Environment Variables, General -> PATH ....and the value was full path of the oracle home directory 'F:\app\rup\product\11.1.0\db_1\bin'

2. In Control Panel, System, Advance, Environment Variables, PATH variable ... and the value was full path of the oracle home directory 'F:\app\rup\product\11.1.0\db_1\bin'

Also, I added ORACLE_HOME in User Defined Environment Variables in Project Properties and the value is 'F:\app\rup\product\11.1.0\db_1'.
ORACLE_SID is another User Defined Environment Variable and its value is 'ORCL'.

These values are without quotes.

Hope that helps. Thanks.

Regards
Rupesh

[chulett]

#1 sets the value for DataStage jobs when they run. #2 sets it for everything all the time. In both cases you should have added the Oracle information to whatever existed there, not replaced what was there with it. And this is on the DataStage Server, yes? Not your client PC I assume.

You don't need to set ORACLE_SID.

[rupeshg]

Thanks Craig.

DS Server, Client and Oracle 11g server are on the same machine.

And, I did not replace the values anywhere. Meaning, PATH variable in Project Properties -> Environment Variables was empty. Also, PATH variable in system environment variables already had the oracle home string. Hence, I did not touch it.

So, I think the values are correct but the job still fails for PATH which I do not understand.

Regards
Rupesh

[chulett]

Unfortunately, I don't "do Windows" so to speak, so don't know all the gotchas for setting up Oracle connectivity there. I'm sure others do, however.

I'm guessing you need the Windows equivalent of the "shared library path" setup, something which is a separate environment variable under UNIX but I'm not sure how it is handled by Windows. And what would go there would be the Oracle lib path, not the bin path. AFAIK.

Sorry I couldn't help more. I'll be curious what the answer turns out to be.