ORACLE connection error

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

ORACLE connection error

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;


Accepted Solutions
Solution
‎04-25-2015 04:55 AM
Valued Guide
Posts: 3,208

Re: ORACLE connection error

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


All Replies
Super User
Posts: 3,110

Re: ORACLE connection error

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.

Contributor
Posts: 42

Re: ORACLE connection error

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. 

Valued Guide
Posts: 3,208

Re: ORACLE connection error

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 --<-----
Contributor
Posts: 42

Re: ORACLE connection error

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?

Valued Guide
Posts: 3,208

Re: ORACLE connection error

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 --<-----
Contributor
Posts: 42

Re: ORACLE connection error

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)

    )

  )

Super User
Posts: 3,110

Re: ORACLE connection error

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.

Contributor
Posts: 42

Re: ORACLE connection error

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

Solution
‎04-25-2015 04:55 AM
Valued Guide
Posts: 3,208

Re: ORACLE connection error

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 --<-----
Contributor
Posts: 42

Re: ORACLE connection error

Thank you for your help!  I greatly appreciate it.

Contributor
Posts: 42

Re: ORACLE connection error

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!!!

Occasional Contributor
Posts: 17

Re: ORACLE connection error

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.

Valued Guide
Posts: 3,208

Re: ORACLE connection error

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 --<-----
Occasional Contributor
Posts: 17

Re: ORACLE connection error

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;

 

☑ This topic is SOLVED.

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

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