Desktop productivity for business analysts and programmers

Able to connect to Oracle in EG but not in batch mode

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Able to connect to Oracle in EG but not in batch mode

Hello,

I'm extracting data from an Oracle DB using PROC SQL. Here's the sample code:

proc sql;

connect to ORACLE (path='SERVICE_NAME' user='LOGIN' password='PASS' preserve_comments);

create table SAS_LIB.DESTINATION_TABLE as

select * from connection to ORACLE (

     select --column names

     from SCHEMA_NAME.SOURCE_TABLE);

disconnect from ORACLE;

quit;

This works perfectly when I run it from within Enterprise Guide.

However when I try to run it in batch mode from the Unix server command line...

nohup  /sas_path/sas -sysin  '/program_path/file_name.sas' &

... I get the following error in the log file:

3 proc sql;

4 connect to ORACLE (path=**** user=**** password=**** preserve_comments);

Could not load /sasbin/SAS/SAS_9.1/sasexe/sasora (39 images loaded)

Error:     0509-022 Cannot load module /sasbin/SAS/SAS_9.1/sasexe/sasora.

    0509-150   Dependent module libclntsh.a(shr.o) could not be loaded.

    0509-022 Cannot load module libclntsh.a(shr.o).

    0509-026 System error: A file or directory in the path name does not exist.

    0509-022 Cannot load module /sasbin/SAS/SAS_9.1/sasexe/sasora.

    0509-150   Dependent module

/sasbin/SAS/SAS_9.1/sasexe/sasora could not be loaded.

ERROR: The SAS/ACCESS Interface to ORACLE cannot be loaded. ERROR: Image SASORA   found but not loadable.

ERROR: A Connection to the ORACLE DBMS is not currently supported, or is not installed at your site.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

Other programs that don't need connection to the Oracle DB work correctly using the above batch submission method.

My guess is that some autoexec program loads the necessary modules when EG starts. I've searched the /sasbin folder in the server but all autoexec files I could find only loaded libnames and created global macro variables, with no reference to Oracle. The server runs SAS engine 9.1.3 and EG version is 4.1.

Any ideas on how I can load the missing modules without the need to open Enterprise Guide?

Thanks

Euripedes


Accepted Solutions
Solution
‎08-31-2013 09:05 PM
Respected Advisor
Posts: 4,137

Re: Able to connect to Oracle in EG but not in batch mode

There are a couple of UNIX environment variables which must be set for this to work (eg. LIBPATH).

Try and use sas.sh in the environment where you want to run your job. That should initiate the SAS environment as you need it.

So your command line should look like:

nohup  </opt/sas/conf/Lev1/SASApp/>sas.sh -sysin  '/program_path/file_name.sas' &

It this still isn't working then you need to investigate how the SAS EG session gets established and you need eventually to set up your own wrapper script (some batch.sh wrapper script around sas.sh which initialises everything additionally you need).

View solution in original post


All Replies
Valued Guide
Posts: 3,208

Re: Able to connect to Oracle in EG but not in batch mode

The used script for batch processing is missing all needed environment settings as deciribed in the configuration guide of the installation for Oracle. It is not an sas-autoexec or sas-config but the scripting at Unix level.

The Eguide WS-server session is also started by some Unix scritping at that one it is solved as it is working.

Ask you Unix support....

---->-- ja karman --<-----
Solution
‎08-31-2013 09:05 PM
Respected Advisor
Posts: 4,137

Re: Able to connect to Oracle in EG but not in batch mode

There are a couple of UNIX environment variables which must be set for this to work (eg. LIBPATH).

Try and use sas.sh in the environment where you want to run your job. That should initiate the SAS environment as you need it.

So your command line should look like:

nohup  </opt/sas/conf/Lev1/SASApp/>sas.sh -sysin  '/program_path/file_name.sas' &

It this still isn't working then you need to investigate how the SAS EG session gets established and you need eventually to set up your own wrapper script (some batch.sh wrapper script around sas.sh which initialises everything additionally you need).

New Contributor
Posts: 4

Re: Able to connect to Oracle in EG but not in batch mode

Hello,

I found the sas.sh file and created a wrapper script that includes it, and now it works like a charm.

Thank you very much!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 1127 views
  • 3 likes
  • 3 in conversation