topic Re: Able to connect to Oracle in EG but not in batch mode in SAS Enterprise Guide

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

Euripedes — Fri, 30 Aug 2013 22:47:05 GMT

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

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

jakarman — Sat, 31 Aug 2013 08:09:20 GMT

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....

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

Patrick — Sun, 01 Sep 2013 01:05:36 GMT

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).

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

Euripedes — Wed, 04 Sep 2013 19:33:17 GMT

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!