topic SAS/ACESS Interface to Oracle could not be loaded fails on batch submit in SAS Programming

SAS/ACESS Interface to Oracle could not be loaded fails on batch submit

SAShole — Mon, 25 Oct 2021 13:28:20 GMT

Hi SAS Frens,

When I submit the code below it works fine (SAS 9.4 Grid on Windows environment) but when I schedule the job to run (SAS Scheduling Server) it fails with the below error. My guess is that the Server does not have the SAS/ACCESS interface. Would Explicit SQL Pass through be any help?

libname oraRXI oracle user=XXXXX pw="XXXXX"
         schema=RXI path="(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=XXX.com)(PORT=XXXX)) (CONNECT_DATA=(SERVICE_NAME=XXXX.com)))";


proc sql;
create table want as select * from have;
quit;

ERROR: The SAS/ACCESS Interface to ORACLE cannot be loaded.

ERROR: Image SASORA found but not loadable..

ERROR: in the LIBNAME statement

ERROR: Libref ORARXI is not assigned.

Re: SAS/ACESS Interface to Oracle could not be loaded fails on batch submit

ChrisHemedinger — Mon, 25 Oct 2021 13:39:36 GMT

I'd check the license/install in your batch job by including proc product_status; run; and reviewing that log.

If SAS/ACCESS to Oracle is not available, then explicit passthrough will not work either. If it is licensed/installed, then your SAS environment still needs to know where your Oracle client is, the TNSNAMES.ora file, etc. These are usually controlled by environment variables and an Oracle or SAS admin can advise.

Just went through this myself and added these variables to my shell script:

export ORACLE_HOME=[path]/oracle/12.2.0.1
export LD_LIBRARY_PATH=[path]/oracle/12.2.0.1/lib
export TNS_ADMIN=[path]/admin

Re: SAS/ACESS Interface to Oracle could not be loaded fails on batch submit

SAShole — Mon, 25 Oct 2021 16:05:12 GMT

I confirmed that SAS/ACCESS Interface is available and have reached out to the SAS Admins about pointing SAS to the Oracle DB. Thank you for the info about TNSNAMES.ora it was helpful.

Re: SAS/ACESS Interface to Oracle could not be loaded fails on batch submit

ChrisHemedinger — Mon, 25 Oct 2021 16:41:18 GMT

In your SAS programs you can check the current values of these environment variables to report them in the SAS log:

%put %sysget(ORACLE_HOME);
%put %sysget(TNS_ADMIN);
%put %sysget(LD_LIBRARY_PATH);