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
The PATH is defined in your TNSNAMES.ORA Oracle config file. This link explains it:
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.
The PATH is defined in your TNSNAMES.ORA Oracle config file. This link explains it:
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.
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
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.
Thank you so much!
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;
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
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.
Thank you so much!!!
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.