SAS Viya now includes database drivers for many enterprise databases in the deployment, including for Oracle, Teradata and DuckDB (and it's a growing list!). These pre-bundled drivers eliminate external client installations, version conflicts, and complex environment configuration, making setting up SAS/ACCESS products and Data Connectors much more convenient. This post walks through an example of connecting to Oracle from your SAS Viya environment using the built-in drivers.
SAS Viya's bundled Oracle Instant Client (currently version 19.22) provides immediate SAS/ACCESS connectivity. No ORACLE_HOME, LD_LIBRARY_PATH, or TNS_ADMIN variables needed; the drivers work out of the box. In fact, there's not much to do at all, except establish a connection.
Method 1: UNC Direct PATH Notation
Connect to Oracle using simple hostname:port/service syntax. In my case, I deployed Oracle XE to a separate namespace in my Kubernetes cluster:
libname ora oracle
path="oracle-xe.oraclexe.svc.cluster.local:1521/XEPDB1"
user=testuser password=TestPass123;
The output should indicate a successful connection:
NOTE: Libref ORA was successfully assigned as follows:
Engine: ORACLE
Physical Name: oracle-xe.oraclexe.svc.cluster.local:1521/XEPDB1
This format passes directly to Oracle without tnsnames.ora lookup or configuration files. SAS Viya's Compute Server automatically uses the drivers which are located at
/opt/sas/viya/home/lib64/accessclients/oracle/19.22/client64.
Method 2: Full EZCONNECT DESCRIPTION String
For complete explicitness, embed the full TNS descriptor, as you would have in your tnsnames.ora file.
libname ora oracle
path='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-xe.oraclexe.svc.cluster.local)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XEPDB1)))'
user=testuser password=TestPass123;
Centrally manage connection info
We also have the option of leveraging a tnsnames.ora file. You may already have one with connections to multiple databases defined, or you could create one to simplify the management and remove some administration overhead. There's a bit more to do in this setup, as we need to make the file available to the SAS Compute Server pod and then tell SAS Viya to use it. I'll cover this in a future post.
Bundled Drivers vs BYO client
Of course, we don't have to use the bundled drivers. We can always use another preferred version of the client. For example, if you need a specific version, or perhaps a client that includes sqlplus or tnsping for help with setup and troubleshooting, which are not included in the Instant Client bundled with SAS Viya. I will address this in my next post.
But, there are some obvious advantages to using the built-in drivers:
- All drivers are pre-installed with no external dependencies
- No need to set up environment variables
- No container/pod configuration required
Conclusion
SAS Viya's inclusion of native SAS/ACCESS database drivers makes Oracle connectivity simple. A correct LIBNAME statement is all that is needed; no TNS complexity, no mounting volumes or pod configuration, and no external client management. Just deploy SAS Viya, write your libname statement and connect to your database.
For further reading, take a look at:
Requirements for SAS/ACCESS Interface to Oracle
How to Configure a SAS/ACCESS interface in SAS Viya 4
Understand the SAS/ACCESS Configuration README File
Configuring Oracle Data Source at Remote Data Agent (Cloud Data Exchange)
My thanks to Henri Lima, Nicolas Robert and Raphael Poumarede for their contributions.
Find more articles from SAS Global Enablement and Learning here.
