When I search for SAS Access to Oracle I tend to find a lot of information on how to code my SAS script, what engine to use and some of the options available, but details on the configuration are few and far between. So, I thought I would write a blog about each one so the next time I search I’ll be able to find a reference.
The key file for Oracle is tnsnames.ora.
Some organisations update the tnsnames.ora file centrally, so you only need to check if your connection string is there. The tnsnames.ora file is typically local to the server so you will need to update it on all of your compute servers.
If using Linux or similar be careful updating it using ftp as this can corrupt the file, I embraced the VI editor.
Now you know what to update and how, you just need to find it. You can search for the tnsnames.ora file, read your SAS installation documentation or ask a friendly Oracle DBA. Here are a couple of places you could look.
When you find the file a connection string will look similar to this. There are a few variations so don’t be concerned if yours is not the same.
(ADDRESS = (PROTOCOL = TCP)(HOST = beam.optimalbi.co.nz)(PORT = 10001))
(SID = beam)