Solving the ORA-12504 Error

Oracle DB ORA-12504 error

When attempting to use the ODBC Data Source Administrator to test a connection to an Oracle Database from an Oracle client – one that did not have TNSPING installed – I got the above error:

Unable to connect
SQLState=S1000
[Oracle][ODBC][Ora]ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

When searching this error, I found quite a few Stack Overflow articles. Unfortunately, none of them solved the problem.

I was able to apply the remedy to a different issue to this one, and fortunately it worked.

For whatever reason, this client, though not the Instant Client as was the case in the previous article, was missing the “.ora” configuration files and the TNS_ADMIN environment variable. Once these were created, the client worked as expected.

Adding Configuration Files to Oracle Database 12c Instant Client

Oracle Database 12c

Happy New Year!

Though I generally prefer to use the full Oracle client, I have many customers that use the Instant Client to save space. However, this setup frequently is missing the configuration files that may be necessary for some applications to be able to find database servers.

If you are missing any of the files sqlnet.ora, ldap.ora, or tnsnames.ora, then this post is for you. In the full client, these files are stored in {ORACLE_HOME}/network/admin; in the Instant Client, this folder may not even exist – but there’s no reason to panic!

First, check to see if you have an environment variable called TNS_ADMIN. If so, this is where your files should be placed. Check to make sure they’re not already there.

If there is no environment variable called TNS_ADMIN, create one and put the full path to the Oracle base folder. Place the files in this folder. Generally, no reboot is necessary.

Sources:
Oracle Instant Client FAQ
Configuring sqlnet.ora and tnsnames.ora on the client side