SQL Developer prompt for substitute variable - how to avoid?

Question

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?

Sandesh Gupta · Accepted Answer · 2018-04-18 17:08:10Z

2

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 #

answered Apr 18, 2018 at 17:08

Sandesh Gupta

1,1952 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mak101 Over a year ago

Dont you think set define off will ignore substitution of csvFile in spool &csvFile ?

Sandesh Gupta Over a year ago

As I mentioned in the 2nd part of answer, you can use any other character instead of & to display prompt.

mak101 Over a year ago

I tried changing '&' to '#' by passing set define #, but no change in result. Its working on one machine and not on other.

Vishal Kumar Over a year ago

This worked like a Magic.

mak101 · Accepted Answer · 2018-04-24 20:32:36Z

0

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

answered Apr 24, 2018 at 20:32

mak101

1472 silver badges12 bronze badges

Collectives™ on Stack Overflow

SQL Developer prompt for substitute variable - how to avoid?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related