- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- I've tried it using the TNS name, but still getting the same error. However, when using the SQL Developer the connection via TNS doesn't work either, with error message: No ocijdbc11 in java.library.path
But it did work when I've used the full path specification. So it maybe has something to do with java?
- The XXX are just placeholders.
- Yes, port 1522 is correct!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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>) ) )´
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!