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

Question

I am running a script that "creates/replaces" a procedure in a database and then runs the procedure. The code is as below:

WHENEVER SQLERROR EXIT

UNDEFINE g_cust_pwd
UNDEFINE g_db_name

SPOOL 'output'

PROMPT setup_and_run.sql begin

ACCEPT g_db_name CHAR PROMPT 'Please enter the DB instance: '
ACCEPT g_cust_pwd PROMPT 'Please enter the TEST_SCHEMA schema password: ' HIDE

PROMPT Testing connection

CONNECT TEST_SCHEMA/&&g_cust_pwd@&&g_db_name

WHENEVER SQLERROR CONTINUE

SET SERVEROUTPUT ON 

SET DEFINE OFF

PROMPT
PROMPT @procedure_test.prc
PROMPT Creating or Replacing Procedure
@procedure_test.prc

PROMPT
PROMPT procedure_test
PROMPT Running Procedure...
EXEC procedure_test

PROMPT setup_and_run.sql complete

SPOOL OFF

UNDEFINE g_cust_pwd
UNDEFINE g_db_name
/

Whenever I run the code above with the correct credentials (double, triple checked these to make sure that this isn't the issue). The code works fine, as expected, the problem occurs whenever I run the code for a second time in the same SQLPlus session it gives a TNS error.

Normally, I would assume that this is merely a TNS names error and the database needs to be added to the list, but it works only once per session (even if I add a disconnect at the end)

Answer 1

In line 21:

SET DEFINE OFF

But you never re-enable it. When you run the script the 2nd time, substitution variables are not replaced with values provided by the user, because DEFINE is still OFF. Just add the below to the start of your script, so variables will be used in consecutive runs:

SET DEFINE ON