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

Hi All,

 

   I am using SAS server under Linux, and I try to connect to oracle data using the follwing syntax, but could not.

 

   Syntax:  libname mydblib oracle  user=*** password=*** path='**';

 

   ERROR: ORACLE connection error: ORA-12170: TNS:Connect timeout occurred.

   ERROR: Error in the LIBNAME statement.

 

   I think maybe I get the path wrong, could you please let me know what's the "Path" mean here and how can I get the "path" information?

 

Thank you!

Jade

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

The PATH is defined in your TNSNAMES.ORA Oracle config file. This link explains it:

 

http://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#p006waw5hf7whbn1xwfq4...

 

You will need to confirm that the server you want to connect to is defined in this file. Consult your Oracle admin for further help.

View solution in original post

9 REPLIES 9
SASKiwi
PROC Star

The PATH is defined in your TNSNAMES.ORA Oracle config file. This link explains it:

 

http://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#p006waw5hf7whbn1xwfq4...

 

You will need to confirm that the server you want to connect to is defined in this file. Consult your Oracle admin for further help.

Jade_SAS
Pyrite | Level 9

@SASKiwi

 

Who define the server in tnsnames.ora file? The SAS admin who install the Oracle client on SAS server or the Oracle admin who in charge of the Oracle server?

 

Thank you!

Jade

JBailey
Barite | Level 11

Hi @Jade_SAS

 

If your SAS admin installed the Oracle client on the server then they may have configured the the TNS Names connection. Typically, configuring, or distributing, a tnsnames.ora file is handled by a DBA or systems administrator. Your SAS Admin may know who to contact have to an entry added, or edited.

 

 

Jade_SAS
Pyrite | Level 9

Thank you so much!

JBailey
Barite | Level 11

Hi @Jade_SAS

 

My pleasure. 

 

If you know the connection information you can bypass the tnsnames.ora. I hesitate to show this because it is very prone to error and can be difficult to get it to work. Here is an LIBNAME statement which match the tnsnames.ora snippet from one of my previous posts: 

 

libname oralib1 oracle path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = ora01-cluster.company.com)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME=exadat12c)))" user=myuser password=mypasswd;

 

Jade_SAS
Pyrite | Level 9

Thank you @JBailey

 

One more question, do you know why we connect Oracle at server level (Path=server SID)? Not at databse or schema level?

Sorry if it is a silly question, I never connect to Oracle before.

 

Jade

JBailey
Barite | Level 11

Hi @Jade_SAS

 

It's not a silly question, at all.

 

Historically, Oracle's highest level was the Oracle Instance. When someone mentions they are connecting to an Oracle database this is what they are talking about. The Oracle instance has users. There is no concept of a schema in Oracle. For example, DB2 has a CREATE SCHEMA statement and Oracle does not. We can fake a schema in Oracle by creating a user which cannot connect to the database. That being said, if you mention an Oracle schema people will understand; DBAs may correct you but probably not. 

 

With the new Oracle Plugable Database architecture the control Oracle instance has databases under it. These databases mimic instances. You connect to the database just like you would an Oracle instance. A DBA will create a tnsnames.ora entry for the plugable database and we, as users, won't know the difference. DBAs have to do things a little bit differently. This construct enables Oracle to provide multi-tenancy. The bit about schemas still holds true - create a user, put tables, indexes, views, synonyms, etc. in it then restrict connection privs.

 

DB2 (remember, it has a SCHEMA object)  is very similar to Oracle.

 

SQL Server and Teradata have the concept of databases, but you still connect to the server in order to get to the database.

Jade_SAS
Pyrite | Level 9

Thank you so much!!!

JBailey
Barite | Level 11

Hi @Jade_SAS,

 

The PATH= option points to a service name which is typically defined in the tnsnames.ora file. The tnsnames.ora files are typically pushed to client machines by an automated process, but they don't have to be. Your DBA can help you verify that your connection information is correct and the that the Oracle instance is running. Here is an example tnsnames.ora entry from my PC:

 

EXADATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora01-cluster.company.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = exadat12c)
    )
  )

To connect to this server I can use this LIBNAME statement:

 

LIBNAME myora ORACLE PATH=exadata USER=myuser PASSWORD=mypasswd;

The error you are getting ORA-12170 indicates that you are trying to connect to an Oracle server and the server is not responding. This indicates that your tnsnames.ora file has an entry for server specified by your PATH= option. The problem could be that the Oracle instance is not running or that there is a 32-bit Oracle client installed on your SAS machine.

 

If the service name specified using PATH= is not found in the tnsnames.ora file you will see an ORA-12154 error message. 

 

You may want to try to ping the Oracle instance using tnsping. Here is an example showing a successful ping and one where the service name (bogus) isn't present in the tnsnames.ora file:

 

C:\Users\myuser\Documents>tnsping exadata

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 09-FEB-2017 07:42:39

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
D:\app\client\product\12.1.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora01-cluster.company.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = exadat12c)))
OK (10 msec)

C:\Users\myuser\Documents>tnsping bogus

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 09-FEB-2017 07:46:17

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:
D:\app\client\product\12.1.0\client_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

C:\Users\myuser\Documents>

Best wishes,

Jeff

 

 

 

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
  • 9 replies
  • 10290 views
  • 4 likes
  • 3 in conversation