BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jan_seidel
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
jan_seidel
Calcite | Level 5

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!

View solution in original post

4 REPLIES 4
jakarman
Barite | Level 11

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).


---->-- ja karman --<-----
jan_seidel
Calcite | Level 5
  • 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!
jakarman
Barite | Level 11

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>) )           )´

---->-- ja karman --<-----
jan_seidel
Calcite | Level 5

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 4 replies
  • 2656 views
  • 4 likes
  • 2 in conversation