CODEWITHSUNDEEP
oracle-sqldevelopersubstitution
mak101
mak101

Reputation: 147

SQL Developer prompt for substitute variable - how to avoid?

I have two scripts master.sql category.sql and I am setting value for substitute variable in master.sql and use it in category value to export data in csv file - the file name is stored in substitute variable. Here is my code:

master.sql

set feedback off;
set serveroutput on;
SET SQLFORMAT csv;
var CatCode char(5) ;
COL fileName NEW_VALUE csvFile noprint

exec :CatCode := '18';
select ('c:\temp\CatCodes_' ||  trim(:CatCode) || '.csv') fileName from dual;
@category.sql;

exec :CatCode := '19';
select ('c:\temp\CatCodes_' ||  trim(:CatCode) || '.csv') fileName from dual;

category.sql

set termout off
spool &csvFile 
SELECT Catgry_ID, Category_Name FROM Categories WHERE Catgry_ID = :CatCode;
spool off;
set termout off

When I run script master.sql (F5) on one machine it just works fine and creates two different csv files in c:\temp folder but when I run same script on different machine it prompts for csvFile! I am sure it should be some setting issue but I can't find it. I checked DEFINE setting on machine where it does not prompt, show define "&" (hex 26) and on another one it shows define "&". Is there anything else do I need to set to ignore prompt?

Upvotes: 0

Views: 1803

Answers (2)

mak101
mak101

Reputation: 147

I ended up removing SQL Developer and reinstalling it and it worked :)

Upvotes: 0

Sandesh Gupta
Sandesh Gupta

Reputation: 1195

Write this in your script 

set define off

If you are using substitution variables, you can set define to some other character(ensure that character is not used elsewhere)

set define <<Character>>

Example:

set define #

Upvotes: 2

Related Questions