CODEWITHSUNDEEP
oracle-databasetoadspooltnsnames
MagicMatt
MagicMatt

Reputation: 33

Oracle SQL predefined variable _CONNECT_IDENTIFIER not working with Spool in Toad

I am using Toad and have a file 'run_script.sql' that is used to run .sql scripts and spools to an output file. This has always worked for me, creating the spooled file in the same directory as the 'run_script.sql' file. However, I recently had to change servers and now the spool isn't working. I've narrowed it down to the use of "_CONNECT_IDENTIFIER" in the spooled file's name - when I remove this variable from the filename, the spooled file generates successfully. Here is the "run_script.sql" file:

set define "&"
define vscript = name_of_script_to_execute
spool &vscript-&_CONNECT_IDENTIFIER.lst
@@vscript..sql
spool off
undefine vscript

This has always worked to generate the .lst file successfully with the name of the database instance included (from tnsnames.ora), until the recent server change. Now, everything runs fine (no errors), but the .lst file is never generated. As stated above, if I remove the predefined variable and change the spool's name to:

spool &vscript.lst

the .lst file is generated successfully, which is why I'm assuming it has to be the _CONNECT_IDENTIFIER that is causing the issue.

When I look at the value of the _CONNECT_IDENTIFIER variable in the User Variables section of the script output's Environment tab (in Toad) after running the script, it differs from what it used to be. Previously, the value was the 'name' of the entry in tnsnames.ora, and now it is the 'value' of that entry.

So previously, the value was:

_CONNECT_INDENTIFIER = DARTTST

This works fine and generates the .lst file via spool. On the new server however, the value is:

_CONNECT_INDENTIFIER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = X.X.X.X)(PORT = XXX))(CONNECT_DATA = (SID = DARTDB)))

And that is not allowing the .lst file to get created. FWIW, this is the entry in tnsnames.ora on both servers:

DARTTST= 
   (DESCRIPTION= 
      (ADDRESS= 
         (PROTOCOL=TCP)
         (HOST=X.X.X.X)
         (PORT=XXX)
      )
      (CONNECT_DATA= 
         (SID=DARTDB)
      )
   )

Does anyone have any idea what is going on or how I can get the _CONNECT_IDENTIFIER property to be 'DARTTST' like in my old server? Thanks so much!

Upvotes: 2

Views: 149

Answers (0)

Related Questions