BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kparker
Quartz | Level 8

I have researched most of the day and tried many solutions; however, I am receiving the following error when I try to connect to an Oracle server using SAS/ACCESS Interface to Oracle.

ERROR: ORACLE connection error: ORA-12154: TNS:could not resolve the connect identifier specified.

ERROR: Error in the LIBNAME statement.

I verified that have access to SAS/ACCESS Interface to Oracle on my computer and I do.  I am able to connect to the server using File>>Open>>ODBC>>select Data Source Name (defined the server in ODBC with no problem).  I have also checked to ensure I have a TNSNAMES.ora file and it is correct.  All instructions states the PATH= name is the alias.  The code I used to try and connect to Oracle is below.  This is not working for me.  Any help is greatly appreciated!

libname MSDSORA oracle

path='LAPROD'

user='laXXXXXX' password='XXXXXXX'

schema=admin

preserve_tab_names=yes

preserve_col_names=yes;

1 ACCEPTED SOLUTION

Accepted Solutions
jakarman
Barite | Level 11

libname x oracle user=myusr1 pw=mypwd1 Schema=?

  path="(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=10.12.102.60)(PORT=1521))     (CONNECT_DATA=   (SERVICE_NAME=NEOLA.BPWDB.MSDS) )     )"

;
The SID/Service_name will make a connection to oracle. There is that conecpt of databases/schemas that is different to SAS libnames.
Using a SAS libname is needing a level you can see only tables as being seen by that schema (database).

With Explicit Sql pass through you do not need that as you can refer those schema as part of the SQL statement. Using a SAS LIBNAME can be confusing as it limited to one schema (some exeptions).

Having the path statement fully qualified in the libname eliminates the TNSNAMES.ora file. No need for the TNSPING test. The TNSPING is working in his own session environment as part of oraclient install and could be using another on as the SAS environment is seeing. This activating/setting of oracle is one of the adjustments in the installation of the SAS sosftware. As you are getting the message of Oracle it looks the SAS/Access Ora modules have been  installed and activated.  Another pitfall can be installing the Oracle client at your desktop seeing that it all is working with that Tnsames, than you run SAS on a server. That definitely not the same TNSNAMES.ora, may be needing some DBA to change that one.   

---->-- ja karman --<-----

View solution in original post

14 REPLIES 14
SASKiwi
PROC Star

Your LIBNAME indicates you are not using ODBC. I would check your PC's TNSNAMES.ORA Oracle config file to see if your path LAPROD is defined in there. You will find this file in the Oracle client install directory.

kparker
Quartz | Level 8

I am trying to use SAS/ACCESS Interface to Oracle.  I am able to access the Oracle server via the ODBC option; however, it is less efficient. I am trying to create a direct connection to the server as I do with OLEDB to my SQL server.  I thought I could do this with the LIBNAME statement but having a problem with the correct PATH. 

jakarman
Barite | Level 11

There is no need to use the TNSNAMES.ora file. Just use all definitions ins tram with your sas-code. The same is possible with ODBC.

See SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition  LIBNAME Statement Specifics for Oracle (example is at the bottom)

The message is saying your Oracle server is not reachable or the TNSNAMES is incorrect.cle

---->-- ja karman --<-----
kparker
Quartz | Level 8

Thank you for your response. Where can I find the PATH name?  I have tried Environmental Variables path, I have tried the server IP and Server name, and I have tried the alias in TNSNAMES.ora file but I continue to get the TNS error.  Any suggestions?

jakarman
Barite | Level 11

The pathname to find the tnsnames is set by en environment variable (OS-level) the one that SAS is seeing does not need to be the same one as you are expecting to be used.

The example:

path="(DESCRIPTION=

          (ADDRESS_LIST=

            (ADDRESS= (PROTOCOL=TCP)(HOST=pinkfloyd)(PORT=1521))

             )

              (CONNECT_DATA=

       "       (SID=alien)

       "   )

       " )

       " ";

is part of the SAS libname. As you are having an ODBC connection working you should know  all that information (schema to be added)

when you have a tnsnames defininition that are the same variables as found on the line with that single name.

---->-- ja karman --<-----
kparker
Quartz | Level 8

Yes, this is already configured.  Below is what I have in my TNSNAMES.ora file.  Do I  need to cut and paste this as the path exactly as below?  Are there any characters I am missing?

LAPROD=

  (DESCRIPTION=

    (ADDRESS=

      (PROTOCOL=TCP)

      (HOST=10.12.102.60)

      (PORT=1521)

    )

    (CONNECT_DATA=

      (SERVICE_NAME=NEOLA.BPWDB.MSDS)

    )

  )

SASKiwi
PROC Star

Try TNSPING LAPROD Oracle command on your PC to confirm if your PC can communicate with the Oracle server or not. If it can't then I would consult an Oracle DBA to confirm if your LAPROD definition is correct or not. If it is correct then there must be something blocking your communication. A firewall without a rule to let your traffic through is one possibility. Consult an IT network specialist to monitor your attempts and isolate where the problem is.

kparker
Quartz | Level 8

TNSNAME PIC.pngIt looks like I can communicate. Thanks for your assistance!

jakarman
Barite | Level 11

libname x oracle user=myusr1 pw=mypwd1 Schema=?

  path="(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=10.12.102.60)(PORT=1521))     (CONNECT_DATA=   (SERVICE_NAME=NEOLA.BPWDB.MSDS) )     )"

;
The SID/Service_name will make a connection to oracle. There is that conecpt of databases/schemas that is different to SAS libnames.
Using a SAS libname is needing a level you can see only tables as being seen by that schema (database).

With Explicit Sql pass through you do not need that as you can refer those schema as part of the SQL statement. Using a SAS LIBNAME can be confusing as it limited to one schema (some exeptions).

Having the path statement fully qualified in the libname eliminates the TNSNAMES.ora file. No need for the TNSPING test. The TNSPING is working in his own session environment as part of oraclient install and could be using another on as the SAS environment is seeing. This activating/setting of oracle is one of the adjustments in the installation of the SAS sosftware. As you are getting the message of Oracle it looks the SAS/Access Ora modules have been  installed and activated.  Another pitfall can be installing the Oracle client at your desktop seeing that it all is working with that Tnsames, than you run SAS on a server. That definitely not the same TNSNAMES.ora, may be needing some DBA to change that one.   

---->-- ja karman --<-----
kparker
Quartz | Level 8

Thank you for your help!  I greatly appreciate it.

kparker
Quartz | Level 8

This worked!!! I had the PATH name incorrect.  When I removed LAPROD=, it allowed me to make a pass-through connection directly to the Oracle server.  Thanks again!!!

Jlochoa
Obsidian | Level 7

I have a similar question about Oracle connection errors using OLEDB. I obtained the following database connection information from the DB Admin.

 

TNS:

DB=
(DESCRIPTION=
(SDU=16384)
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=DB.xyz.com)
(PORT=1522)
)
(ADDRESS=
(PROTOCOL=TCP)
(HOST=it346.xyz.com)
(PORT=1522)
)
)
(CONNECT_DATA=
(SERVICE_NAME=DB_DG.xyz.com)
)
)

 

I've been unsuccessful with the LIBNAME statement using OLEDB. For example,

 

libname mylib oledb INIT_STRING="PROVIDER=OraOLEDB.Oracle.1;Password=######;Persist Security Info=True;User ID=######; HOST=DB.xyz.com;Data Source=DB;PORT=1522";

 

I know that the above statement works on other DBs.

jakarman
Barite | Level 11

You will need a OLEDB provider. Something like: http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html

There is a paper http://www.lexjansen.com/mwsug/2004/Pharmaceutical/P3_Zagorski.pdf mentioning MS oracle, but at MS they tell you to move on to the Oracle tool.

Then look at OLEDB Oracle connection strings The TNS-less option is the one in your wanted direction.

https://www.connectionstrings.com/oracle-provider-for-ole-db-oraoledb/

 

Altough I never tried this one (oledb) I think you have all needed info in the several doc's to get joined.   

 

 

---->-- ja karman --<-----
Jlochoa
Obsidian | Level 7

Bingo!!! You are the man!!!

 

The solution was setting the Data Source as:

 

TNS-less connection string

Provider=OraOLEDB.Oracle;
Data Source
=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myHost)(PORT=myPort)))(CONNECT_DATA=(SID=MyOracleSID)(SERVER=DEDICATED)));
User Id
=myUsername;Password=myPassword;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 28934 views
  • 1 like
  • 4 in conversation