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

[ibrwa]

Dear SAS community, 

I'm trying to connect to Oracle with Libname statement but I got an error. I have done lot of research and checks but I'm blocked. I try to run the following libname statement: 

libname ORGORA Oracle user=ORG password="ORG" schema=ORG_SYS path="ORG";

I have the following error: 

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

Oracle and SAS are installed in the same server. I have made a look in the tnsnames.ora and I can find the path correctly defined: 

ORG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxxx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORG)
)
)

I then check that in the server I can ping the path ORG which works fine also: 

[oracle@xxxxx ~]$ tnsping ORG

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 25-MAR-2022 11:22:48

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORG)))
OK (0 msec)

I have also check in SQL developer, and I can connect without any issues. Please see the attachment. I have restarted the database, restarted the listener but I'm blocked. 

Can you please help me ? 

Thank you very much in advance. 

[Sajid01]

Hello
Successful connection with SQL developer means you have the proper credentials.
 Try using the following libname statements 

libname ORGORA Oracle user=ORG password=ORG path=ORG;

I

[Patrick]

Have you already seen this discussion?
https://communities.sas.com/t5/SAS-Programming/ORACLE-connection-error/td-p/201064 

[ibrwa · Accepted solution]

Hello @Patrick 

Thank you for your reply. I have solved the issue. So what happens? When I configured the Oracle client for SAS access to Oracle I relied on this tutoriel: https://blogs.sas.com/content/sgf/2017/04/25/how-to-configure-oracle-client-for-successful-access-to...

The tuto explains this: To configure the Oracle client, create a file called tnsnames.ora, for example, in the /etc/ directory. Paste the following lines with the appropriate connection parameters of your Oracle DB into the tnsnames.ora file. (Replace"your_tnsname", "your_oracle_host", "your_oracle_port"and"your_oracle_db_service_name"with parameters according to your Oracle DB implementation)

However I think that it is not a good practice. Why ? The tnsnames.ora used by Oracle software can be updated if you create a new service name like I did. So if you create a separate tnsnames.ora in /etc you need to update it each time when the original tnsnames.ora is modified. 

I my situation I forgot to update the file /etc/tnsnames.ora. Thus, my new service names ORG is not present in /etc/tnsnames.ora then I got the issue. It is better to use TNS_ADMIN variable directly used in Oracle which is: $ORACLE_HOME/admin/network then you I don't need to each time update the file /etc/tnsnames.ora. 

But finally yes I have solved my issue.

Thank you for your help.

[Patrick]

@ibrwa Thank you for sharing with the SAS community. Also feel free to add a comment to the referenced blog if you think there is a better or alternative way how to do this. I'm sure this will be helpful to others facing similar challenges in the future.