What's the path in syntax of SAS connection to Oracle data?

[Jade_SAS]

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

[SASKiwi · Accepted solution]

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]

@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]

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]

Thank you so much!

[JBailey]

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]

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]

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]

Thank you so much!!!

[JBailey]

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