Connecting to Oracle from SAS Viya 4 on AWS

thesasuser — Thu, 23 May 2024 19:57:02 GMT

Hello
I have been trying to test connection to on premises Oracle installation from a POC of SAS Viya4 on AWS. SAS Access Interface to Oracle is available.
I have followed the wonderful tutorial on the subject at

SAS Demo | How to Configure a SAS/ACCESS Interface in SAS Viya 4

and followed the process with local adaptations. But I get the following error

ERROR: Unable to load oracle client (libclntsh.so) ERROR: Error in the LIBNAME statement.

when trying to connect from SAS studio using a simple libname statement (as shown in the above tutorial.

libname test oracle user=validUser password=validPath path="\\validHostname.example.com:1521" SCHEMA=validSchema;

The user, password etc work in normal SAS installation.

I executed the following code in SAS Studio to see if the environment variables are available to SAS or not. The code and output are reproduced below

81 data _null_; 82 if sysexist('PATH') then do; 83 path = sysget('PATH'); 84 put path=; 85 end; 86 else do; 87 put "PATH does not exist"; 88 end; 89 if sysexist('ORACLE_HOME') then do; 90 Oracle_Home = sysget('ORACLE_HOME'); 91 put Oracle_HOME=; 92 end; 93 else do; 94 put "ORACLE_HOME does not exist"; 95 end; 96 if sysexist('ORACLE_BIN') then do; 97 Oracle_Bin = sysget('ORACLE_BIN'); 98 put oracle_bin=; 99 end; 100 else do; 101 put "ORACLE_BIN does not exist"; 102 end; 103 if sysexist('TNS_ADMIN') then do; 104 Tns_admin = sysget('TNS_ADMIN'); 105 put Tns_admin=; 106 end; 107 else do; 108 put "TNS_ADMIN does not exist"; 109 end; 110 if sysexist('LD_LIBRARY_PATH') then do; 111 LD_LIB_PATH = sysget('LD_LIBRARY_PATH'); 112 put LD_LIB_PATH=; 113 end; 114 else do; 115 put "LD_LIB_PATH does not exist"; 116 end; 117 run; path=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/access-clients/Oracle/instantclient:/opt/sas/viya/home/lib64 Oracle_Home=/access-clients/Oracle/instantclient Oracle_Bin=/access-clients/Oracle/instantclient Tns_admin=/access-clients/Oracle/instantclient/network/admin LD_LIB_PATH=/opt/sas/viya/home/lib64:/usr/lib/jvm/java-17-openjdk-17.0.9.0.9-2.el8.x86_64/lib/server:/usr/lib/jvm/java-17-openjdk-17 .0.9.0.9-2.el8.x86_64/lib:/opt/sas/viya/home/SASFoundation/sasexe:/access-client NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds

From the above code output,it is clear that the instant client path and libraries are clearly visible.
I feel SAS Viya 4 on the cloud must have been around for some time and other users must have successfully accomplished this task.
I would appreciate if any of them can share their thoughts about what could have gone wrong.
PS : I did see this post with similar error and feel the resolution would not work for me

https://communities.sas.com/t5/Administration-and-Deployment/SAS-Access-Oracle-Configuration/m-p/927516

Re: Connecting to Oracle from SAS Viya 4 on AWS

thesasuser — Fri, 07 Jun 2024 15:12:27 GMT

Hello
The issue was resolved using the following steps
1.Using the proper oracle instant client basic. We had oracle version 12.2
2.Whitelisting SAS Viya 4 in the VPD databases

topic Connecting to Oracle from SAS Viya 4 on AWS in Moving to SAS Viya

Connecting to Oracle from SAS Viya 4 on AWS

Re: Connecting to Oracle from SAS Viya 4 on AWS