"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.

Boneist · Accepted Answer · 2017-05-12 11:08:04Z

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.

edited May 12, 2017 at 11:08

answered May 10, 2017 at 12:56

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

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

4 Comments

Mannu Over a year ago

thanks for the response @Boneist. However, I am getting error as - SP2-0556: Invalid file name. not spooling correctly

Mannu Over a year ago

The first code doesn't ask for folderpath value. Second code asks for value of folderpath but throws above mentioned error (in other comment).

Boneist Over a year ago

I've updated my answer to remove the @s from the beginning of the spool file names - that's not valid and was causing the error. I've also included how you reset the substitution variables so that you will be re-prompted for the value (i.e. use undefine) when you rerun the script.

Mannu Over a year ago

It worked! Since double ampersand stops Oracle from prompting for a value each time while referencing the same variable, there is no need to use another variable folderpath now. undefined was also helpful. thank you for the support!

Roger Cornejo · Accepted Answer · 2017-05-10 12:52:27Z

0

that "set" should probably be a "define"

answered May 10, 2017 at 12:52

Roger Cornejo

1,5571 gold badge8 silver badges7 bronze badges

1 Comment

Mannu Over a year ago

thanks for the response @Roger Cornejo. However, I am getting error as - SP2-0556: Invalid file name. not spooling correctly

Roger Cornejo · Accepted Answer · 2017-05-11 15:16:29Z

0

Try:

spool &folderpath./mycsvfile2.csv

Example from my code:

spool c:\temp\SQL_Details_Report_&FILE_NAME..html

answered May 11, 2017 at 15:16

Roger Cornejo

1,5571 gold badge8 silver badges7 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related