3

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.

Mannu
  • 33
  • 1
  • 4

3 Answers3

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.

Boneist
  • 22,910
  • 1
  • 25
  • 40
  • thanks for the response @Boneist. However, I am getting error as - **SP2-0556: Invalid file name. not spooling correctly** – Mannu May 11 '17 at 14:43
  • The first code doesn't ask for `folderpath` value. Second code asks for value of `folderpath` but throws above mentioned error (in other comment). – Mannu May 11 '17 at 14:51
  • 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. – Boneist May 11 '17 at 15:05
  • 1
    **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!** – Mannu May 12 '17 at 10:21
0

that "set" should probably be a "define"

Roger Cornejo
  • 1,507
  • 1
  • 8
  • 7
  • thanks for the response @Roger Cornejo. However, I am getting error as - **SP2-0556: Invalid file name. not spooling correctly** – Mannu May 11 '17 at 14:47
0

Try:

spool &folderpath./mycsvfile2.csv

Example from my code:

spool c:\temp\SQL_Details_Report_&FILE_NAME..html
Roger Cornejo
  • 1,507
  • 1
  • 8
  • 7