A quick guide to connecting to Oracle from SAS

7 Likes

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:

EXADATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora01-cluster.company.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (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 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 bogusTNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 
- 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

C:\Users\myuser\Documents>

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.

VivekRathod · ‎05-17-2018

Select oracle server

Type in any name for oracle connection server

If you know then enter the oracle database version, however it is not mandatory.

Associated machine – enter the hostname/computername where database is installed.

Path: servername (you will find the path inside the tnsnames.ora file at the oracle installation location)

Example: D:\app\Student\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

Authentication type: keep userid/password only

Authentication domain: create a new authentication domain - > type in OraAuth

Next >> finish.

Now right click on the library folder under library manager >> create new library >> select oracle library >>

Give any name for your library and choose the metadata folder location.

Select sasApp

Then give any libref name for your oracle library

Then select the recently created oracle server name for database server.

For schema name you can find it in sql plus.

So here the owner is nothing but the schema name: educ.

Enter educ in the schema name.

Then right click on the library, then display libname statement

Copy the libname statement and submit in base sas software and check whether the library is created and you are able to find some tables.

LIBNAME oralib ORACLE PATH=ora11g SCHEMA=educ USER=educ PASSWORD="{SAS002}83FBCB5C120105A2298B4E76" ;

Now go back to sas management console and register the oracle tables. Right click on the oracle library and register tables.

JanAnkerKristensen · ‎09-13-2018

Is it possible to convert this to a statement that can be used in a pass through session instaed.

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

JBailey · ‎09-13-2018

Hi @JanAnkerKristensen

You can do this...


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

   execute (CREATE TABLE JEFFTEST (col1 varchar2(20))) by oracle;
quit;

and you can do this...


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

proc sql;
   connect using oralib1;
   select * from oralib1.mytable;
quit;

Hope this helps!

Best wishes,

Jeff

mbakare · ‎12-02-2018

I am having similar problem right now. I connect to oracle 11g xe edition from SAS 9.4 with statement on system running windows 10 enterprise OS.Though I was able to connect successfully through sql-plus and oracle sql developer but not through SAS 9.4.

libname myora oracle user=Moshood password=Agbaman path=XE;

The error I got reads.....

ERROR: The SAS/ACCESS Interface to ORACLE cannot be loaded. ERROR: Image SASORA found but not

loadable..

Please make sure Oracle environment is set correctly.

Look in the install/Config doc for additional info for your platform.

Other possible reasons - incomplete Oracle client install, 32/64-bit mismatch between Oracle

client & SAS, incorrect Oracle client version(Oracle client must match the version

picked during post-install process), incompatible sasora for your OS or its attribs

don't permit SAS to load it.

ERROR: Error in the LIBNAME statement.

JBailey · ‎12-03-2018

Hi @mbakare

Validate that the bitness of the Oracle client matches the bitness of SAS. The tnsping utility can be used to validate the bitness of the Oracle client. Here is an example:

In your case the tnsping command would look like...

tnsping XE

I hope this helps...

Best wishes,

Jeff

Sajid01 · ‎10-02-2019

I know that this guide was written a while ago, but I think this is the very relevant and updated information on the subject..
Therefore I am posting my question here.

I was reading the paper
"AT004: The Dark side of the transparent moon--Tips, Tricks and Traps of handling Oracle Data Using SAS" by Xinyu Ji. (NESUG 16)( https://www.lexjansen.com/nesug/nesug03/at/at004.pdf )
In the introduction section of the paper the author writes
"there is a database account for the user 'samji'... the data resides in the path='nesug' and the public schema is 'sasusers' ".
The author then creates a libname statement as follows.
libname nesug ORACLE user=samji pw=.. path='nesug' schema=sasuser;
I don't understand the word public schema.
My understanding is that oracle has a public role and schema is the metadata of all the objects by a user and has the same name as the login. Thus in instant case for the user 'samji' schema will be 'samji'.

Using the above libname the author runs proc contents on the library.
I don't understand the term public schema 'sasusers'.
Can you please enlighten on this ?

JBailey · ‎10-02-2019

Hi @Sajid01

Oracle does not have schemas; it has users. In DB2, a user can issue a "CREATE SCHEMA" DDL statement. You can't do that in Oracle.

It is common for Oracle DBAs to mimick schemas by creating a user, creating a bunch of objects under this user, then revoking connection privileges. In other words, it is a user who owns objects, but cannot connect to the database.

In the paper, SASUSERS is actually an Oracle user. I believe that the author refers to SASUSERS as a "public schema" because it is a set of database objects used by many people. The SCHEMA=sasusers enables the SAMJI user to transparently access the tables, view, etc. that are owned by SASUSER.

The author's description of PATH= is a little off, too. Here is how the author describes an Oracle Path:

The data of interest reside in the path called "nesug".

An Oracle Path contains the technical information (hostname, port, SID) needed to connect to an instance of Oracle. In other words, the Oracle database. I used an in-line path in the article. If you are interested in the details, take a look at it.

Best wishes,
Jeff

Sajid01 · ‎10-02-2019

Thanks Jeff for your explanation

JBailey · ‎10-02-2019

Hi @Sajid01

My pleasure.

Best wishes,
Jeff

A quick guide to connecting to Oracle from SAS

Free course: Data Literacy Essentials

Get Started