Error viewing data from Oracle

bala_135 · Post by **bala_135** » Thu Nov 24, 2005 4:45 am

Hi,

I am trying to connect to oracle database. I have given the tablename,username,password and connection string.In server 7.5.2 it is working but in parallel i am geeting the following error when I am trying to view the data

##I TOSH 000002 16:15:38(001) <main_program> orchgeneral: loaded
##I TOSH 000002 16:15:38(002) <main_program> orchsort: loaded
##I TOSH 000002 16:15:38(003) <main_program> orchstats: loaded
>##E TOSH 000205 16:15:39(000) <main_program> PATH search failure:
>##E TOSH 000000 16:15:39(001) <main_program> Error loading "orchoracle": Could not load "orchoracle": The specified module could not be found.
>##E TOSH 000000 16:15:39(002) <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 16:15:39(003) <main_program> Creation of step finished with status = FAILED.

Kindly help me to resolve the problem.

Thank you,
Bala

ArndW · Post by **ArndW** » Thu Nov 24, 2005 5:01 am

This looks very much like a configuration issue. Is this the only job with a problem and has Oracle been accessed from PX in this installation already?

PX requires a number of steps to be taken and grants to be made in order to function correctly. If this issue isn't limited to your one job, then you need to look at the installation instructions and make sure that all changes have been made.

bala_135 · Post by **bala_135** » Thu Nov 24, 2005 5:10 am

Hi,

Thank you for the reply. Kindly let me is this an oracle installation issue or Datastage installation problem.Do I need to configure something in Datstage.I have installed datastage first and then Oracle .Does this has any impact.Is the problem because I am using 7.5.2 and installed Oracle 8.Kindly let me know.

Thank you,
Bala

ArndW · Post by **ArndW** » Thu Nov 24, 2005 5:19 am

Did you follow the specific configuration steps outlined for Oracle in the installation guide? It looks like you might have missed these.

If you can connect to Oracle from the DataStage server machine using sqlplus or whatever access tool you normally use then it is a DS configuration issue - otherwise you need to sort out the Oracle configuration problems first.

bala_135 · Post by **bala_135** » Thu Nov 24, 2005 6:23 am

Hi,

Thanks again. I could view the data with the same oracle connection for server jobs but i am having the problem when i try to view the data with parallel jobs,so I guess the problem exist with Datastage parallel configration

ArndW · Post by **ArndW** » Thu Nov 24, 2005 7:28 am

Yes, you need to revisit your installation steps per the guide.

jasper · Post by **jasper** » Thu Nov 24, 2005 7:46 am

check the dir $DSHOME/../PXEngine/lib You here need to see links like -rwxr-xr-x 1 PDdtsadm PDdtsadm 1811572 Aug 9 2004 liborchoracle8isun4.so
-rwxr-xr-x 1 PDdtsadm PDdtsadm 1811792 Aug 9 2004 liborchoracle9isun4.so
-rwxr-xr-x 1 PDdtsadm PDdtsadm 1806720 Aug 9 2004 liborchoracle10gsun4.so
lrwxrwxrwx 1 PDdtsadm PDdtsadm 22 Feb 7 13:34 liborchoraclesun4.so -> liborchoracle9isun4.so
lrwxrwxrwx 1 PDdtsadm PDdtsadm 22 Feb 7 13:34 orchoracle9i.so -> liborchoracle9isun4.so
lrwxrwxrwx 1 PDdtsadm PDdtsadm 22 Feb 7 13:34 orchoracle8i.so -> liborchoracle8isun4.so
lrwxrwxrwx 1 PDdtsadm PDdtsadm 23 Feb 7 13:34 orchoracle10g.so -> liborchoracle10gsun4.so
lrwxrwxrwx 1 PDdtsadm PDdtsadm 20 Feb 7 13:34 orchoracle.so -> liborchoraclesun4.so

bala_135 · Post by **bala_135** » Thu Nov 24, 2005 7:54 am

Hi Jasper,

Thank you very much. Datastage installation is in Windows not in UNIX.Can u help me to resolve it.

ArndW · Post by **ArndW** » Thu Nov 24, 2005 8:02 am

bala_135,

open up the installation guide (you can also find it in your client docs directory as the file "instconf.pdf") and follow the configuration instructions for ORACLE to the letter. Once you have done that and confirmed that you haven't missed out any steps and it still doesn't work then someone here can help, but not really before.

The PATH mechanism in Windows is similar to that of UNIX. Have you checked your path? Have you located the "orchoracle" object in Windows and seen if that directory is in your PATH?

thespecialist · Post by **thespecialist** » Mon Jan 09, 2006 8:35 am

bala_135 wrote: Thank you very much. Datastage installation is in Windows not in UNIX.Can u help me to resolve it.

Bala_135,
Have been able to solve your problem? B'coz I see the same log, when I try to view data in Oracle in PX on windows.
First time I had an Oracle installation before installing PX, at that time it said that the user does not have access to sys.dba_extents table in oracle. For that I reinstalled Oracle9i again and this time made sure that the user has this access, but now it gives exactly the same error log as that of yours.
Please let me know if you got any leads in this regard.

bala_135 · Post by **bala_135** » Mon Jan 09, 2006 8:47 am

Hi,

Still I could not resolve it. There is some configration issue with datastage.
Anyhow I am able to read and write the data through DynamicRDBMS stage but I am not sure of the pros and cons of this with Oracle stage.

Regarads,
Bala.

reachthiru · Post by **reachthiru** » Tue Jan 10, 2006 9:15 am

Hi,

In your environmental variables, you make sure that when you have defined the PATH variable, APT_ORCHHOME/bin is appearing before the ORACLE_HOME/bin folder. You can see this information in the help document. In fact, I also got this error when I wrongly mentioned the values at the job level. After changing it, the job ran successfully. In fact, you also can remove those job level variables and try again.

Eric · Post by **Eric** » Tue Jan 10, 2006 10:18 am

Don't forget $ORACLE_HOME/lib

bala_135 · Post by **bala_135** » Wed Jan 11, 2006 3:46 am

Hi Thiru/Eric,

Thank you very much.I tried the following.

APT_ORCHHOME\bin ;C:\Oracle\Ora81\bin;C:\Program Files\Oracle\jre\1.1.7\bin;C:\PROGRA~1\MKSTOO~1\bin;C:\PROGRA~1\MKSTOO~1\bin\X11;C:\PROGRA~1\MKSTOO~1\mksnt;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;;C:\Ascential\ODBCdrivers in the path of the syatem vaiable.Its still givining me the same error.

Any suggestions.

Thank you,
Bala

antojj · Post by **antojj** » Wed Jan 11, 2006 5:01 am

I think you have to install the ODBC Drivers from the Ascential Datastage CD.

I even faced the same problem just 1 hr back and gone thru the product documentation as mentioned by Arndw.

The driver for Oracle is : Ascential Oracle Wire Protocol
The DLLs for the driver are : VMORA19.dll , VMOR819.dll

open up the installation guide (you can also find it in your client docs directory as the file "instconf.pdf") and follow the configuration instructions for ORACLE to the letter.

I Installed the ODBC drivers in the Datastage Server.
I connected to Oracle from my Datastage client using a ODBC connection. I am able to see the table structure from oracle after providing the username/ password.

bala_135, may be you have to check with your ODBC Driver installation.

Regards,