We’re smarter together. Learn from this collection of community knowledge and add your expertise.

A quick guide to connecting to Oracle from SAS

by SAS Employee JBailey ‎02-11-2017 01:30 PM - edited ‎03-20-2017 02:38 PM (8,377 Views)

You try to connect to Oracle from SAS/ACCESS Interface to Oracle and you get an ORA-12170 error. What could this mean? And what, exactly, is this PATH= thing?



LIBNAME myora ORACLE PATH=orasrv USER=myuse PW=mypassword:



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

    ERROR: Error in the LIBNAME statement.


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. If SAS and Oracle are running on the same machine it is possible to connect without using the PATH= option. This is called a bequeath connection. This is uncommon, so let's take a look at an example tnsnames.ora entry from my PC:



      (ADDRESS = (PROTOCOL = TCP)(HOST = ora01-cluster.company.com)(PORT = 1521))
      (SERVICE_NAME = exadat12c)



To connect to the Oracle server described by the above tnsnames.ora enty I can use this LIBNAME statement:



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


The ORA-12170 error indicates that the Oracle server you are trying to connect to 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 down, you have an incorrect port specified, or there is a 32-bit Oracle client installed on your 64-bit 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. 


The Oracle tnsping utility can help in this situation. tnsping will use information from the tnsnames.ora file to check to see if the Oracle instance is up-and-running. Here are two examples: 1) shows a successful ping 2) shows what happens when 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 - Production on 09-FEB-2017 07:42:39

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
(HOST = ora01-cluster.company.com)(PORT = 1521)))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = exadat12c))) OK (10 msec) C:\Users\myuser\Documents>tnsping bogusTNS Ping Utility for 64-bit Windows: Version
- Production on 09-FEB-2017 07:46:17Copyright (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



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. This is useful when you can't edit the tnsnames.ora file but you know what the entry should look like. The following LIBNAME statement matchs the tnsnames.ora snippet from the previous example: 



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


Wow, that's ugly. It is easy to make a mistake when trying this technique.


Do you know why we connect Oracle at server level (Path=server SID)? Not at databse or schema level?


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.


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


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.



Your turn
Sign In!

Want to write an article? Sign in with your profile.

Looking for the Ask the Expert series? Find it in its new home: communities.sas.com/askexpert.