SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 169
Accepted Solution

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

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


Accepted Solutions
Solution
‎02-09-2017 10:44 AM
Super User
Posts: 3,252

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

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


All Replies
Solution
‎02-09-2017 10:44 AM
Super User
Posts: 3,252

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

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.

Regular Contributor
Posts: 169

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

@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

SAS Employee
Posts: 215

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

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.

 

 

Regular Contributor
Posts: 169

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

Thank you so much!

SAS Employee
Posts: 215

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

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;

 

Regular Contributor
Posts: 169

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

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

SAS Employee
Posts: 215

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

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.

Regular Contributor
Posts: 169

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

Thank you so much!!!

SAS Employee
Posts: 215

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

[ Edited ]

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

 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 459 views
  • 3 likes
  • 3 in conversation