:

ORA-12154: TNS:could not resolve the connect identifier specified

I came across a little Oracle issue this morning which can be quite confusing at times. It’s to do with compilation of a package which has references across a database link. Here is what we were seeing from our database warehouse:

USER1@DW> EXEC CONFIG_PKG.UPDATESOURCEROW

ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified
ORA-06512: at "USER1.UPDATESOURCEROW", line 8
ORA-06512: at "USER1.CONFIG_PKG", line 166
ORA-06512: at line 1

Within the CONFIG_PKG package there is a procedure called UpdateSourceRow which has a reference across a database link:

SourceUser.TmStampUpdate@DW2SOURCE

So we checked out that the database link was working:

SELECT *
FROM table@dw2source
WHERE seqno=123456;

POLMAINSEQNO NAME
------------ ------------------
123456       TEST

OK, so that works fine from the command prompt but it still doesn’t work from the package…It turns out that we needed to recompile the package first:

USER1@DW> alter package CONFIG_PKG.UPDATESOURCEROW compile body;

This compiled without a problem and then the package executed properly. I believe this is because when we refresh our environments from our PROD environment to DEV there are DB links which need to be changed. When we do this the packages still seem to hold onto the PROD database link information which is not available to use in the DEV environment.

You may find that you are hitting a similar error, ORA-12514: TNS: listener does not currently know of service requested in connect descriptor which I have written a much more detailed article about so check that out.

Like it, share it...

Category: Database Config


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *