Hi all,
I'm trying to connect to a Oracle data base using SAS 9.3 with a 4.3 EG client within a VDI environment. I'm using the following LIBNAME statement
libname MYLIB oracle
path='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=XXX)(PROTOCOL=TCP)(HOST=HostXXX)(PORT=1522)))(CONNECT_DATA=(SID=XXX)))'
user=xxx
password='xxx'
preserve_tab_names=yes
preserve_col_names=yes
schema=ORASCHEMA;
and get the following error
ERROR: ORACLE connection error: ORA-12170: TNS:Connect timeout occurred.
I've tried to connect via SQL Developer from within the same VDI environment and it worked (and I also can see data). Does the VDI environment cause the problem and if yes, how to come around?
Any ideas, comments are appreciated.
Thanks,
Jan
I've removed schema specification and tried the layout you've suggested, without success. However, meanwhile I had chat with the guys who're running the SAS environment. Currently they see two issues: 1. the SAS server "doesn't know" the data base connection, 2. the data base is sitting behind a firewall
The latter one, I thought, is not a problem, because I'm executing SAS EG client from within a VDI environment that is actually located *behind* the firewall. But that's of course not true. In a client server set-up it doesn't matter where the client is located. My major learning for today 🙂
Though problem is not solved yet, I'll close this discussion here. I think the SAS environment guys and the Oracle guys have to sort that out.
Thanks for your comments Jaap!
Why are you using the schema definition and the full path?
The schema definition is the one related to the TNSNAMES file that should be configured to your sas sessions (environment variable).
The full path definition is the line that should exactly match what is known in the TNSNAMES, effectively replacing all that.
A TNSNAMES file is not necessary in this way of coding. Every typo will cause an error.
Strange to see al those XXX there, that cannot be correct. Are you sure about 1522 normally seen is 1521. (oracle server default).
But it did work when I've used the full path specification. So it maybe has something to do with java?
The string in SAS should be exactly the same as the one you got working in SQL developer. That are a lot of placeholders any typo will a possible cause for a failing connect.
Did you remove the schema specification with your libname?
This layout worked... (not java related. Ora client may be. Not really different
path=´(DESCRIPTION= (ADDRESS = (PROTOCOL = TCP) (HOST = <dns oracle-server>) (PORT = <port oracle-server>) ) (CONNECT_DATA = (SID=<ora sid>) ) )´
I've removed schema specification and tried the layout you've suggested, without success. However, meanwhile I had chat with the guys who're running the SAS environment. Currently they see two issues: 1. the SAS server "doesn't know" the data base connection, 2. the data base is sitting behind a firewall
The latter one, I thought, is not a problem, because I'm executing SAS EG client from within a VDI environment that is actually located *behind* the firewall. But that's of course not true. In a client server set-up it doesn't matter where the client is located. My major learning for today 🙂
Though problem is not solved yet, I'll close this discussion here. I think the SAS environment guys and the Oracle guys have to sort that out.
Thanks for your comments Jaap!
Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.