"Unknown set option" in SQL developer which creating Spool script

Question

My code is as below-

set define on;
set spool on;
set SQLFORMAT csv;
set fpath = &folderpath;
spool @fpath/mycsvfile1.csv
select * from I_PTY;
spool off;
spool @fpath/mycsvfile2.csv
select * from I_LEG;
spool off;

I want to pass folderpath value as 'C:\Documents\Spool'. And use this value to pass to the variable fpath, so that the value passed in line5 equals to 'C:\Documents\Spool\mycsvfile1.csv'

However, I am getting error as: Unknown set option "fpath".

Where am I getting wrong? Kindly assist.

Answer 1

You could do this in two ways:

Just use &&folderpath in the spool commands directly, e.g.:

set spool on;
set sqlformat csv;

spool &&folderpath/mycsvfile1.csv
select * from i_pty;
spool off;

spool &&folderpath/mycsvfile2.csv
select * from i_leg;
spool off;

Or use DEFINE to assign the value (which you'd then have to reference in the same way as the previous answer, so you don't gain anything, really...):

set spool on;
set sqlformat csv;
define fpath = &folderpath

spool &&fpath/mycsvfile1.csv
select * from i_pty;
spool off;

spool &&fpath/mycsvfile2.csv
select * from i_leg;
spool off;

Using the double ampersand stops Oracle from prompting you for a value each time you reference the same substitution variable. If you want your next script or next run through of this script to be prompted again for the value of folderpath, then you will need to include undefine folderpath (and maybe undefine fpath) at the end of the script.

Also, you may want to include set verify off at the top of your script.

N.B. you were using @ in your spool file name - that has no place there. It's only used when you're running a script.