topic Re: ORACLE connection error in SAS Programming

ORACLE connection error

kparker — Tue, 21 Apr 2015 20:16:23 GMT

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;

Re: ORACLE connection error

SASKiwi — Tue, 21 Apr 2015 20:47:37 GMT

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.

Re: ORACLE connection error

kparker — Fri, 24 Apr 2015 15:40:28 GMT

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.

Re: ORACLE connection error

jakarman — Fri, 24 Apr 2015 15:56:41 GMT

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

Re: ORACLE connection error

kparker — Fri, 24 Apr 2015 16:06:17 GMT

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?

Re: ORACLE connection error

jakarman — Fri, 24 Apr 2015 16:21:05 GMT

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.

Re: ORACLE connection error

kparker — Fri, 24 Apr 2015 16:43:24 GMT

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)

)

Re: ORACLE connection error

SASKiwi — Sat, 25 Apr 2015 05:27:12 GMT

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.

Re: ORACLE connection error

jakarman — Sat, 25 Apr 2015 08:55:30 GMT

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.

Re: ORACLE connection error

kparker — Mon, 27 Apr 2015 14:58:11 GMT

It looks like I can communicate. Thanks for your assistance!

Re: ORACLE connection error

kparker — Mon, 27 Apr 2015 14:59:38 GMT

Thank you for your help! I greatly appreciate it.

Re: ORACLE connection error

kparker — Mon, 27 Apr 2015 15:05:36 GMT

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

Re: ORACLE connection error

Jlochoa — Mon, 26 Oct 2015 13:56:14 GMT

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.

Re: ORACLE connection error

jakarman — Mon, 26 Oct 2015 20:03:51 GMT

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.

Re: ORACLE connection error

Jlochoa — Mon, 26 Oct 2015 20:33:53 GMT

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;