Search code examples
oraclesqlplusspool

Spooling to a file with a name containing a space and script's parameter in sqlplus?

I'm trying to change a script with a parameter to spool the output into specific path. After searching Stack Overflow I came up with:

column filename new_val filename
select '"i:\Direct bank\incoming\ROSE\report_zgod_' || '^1' ||'.csv"' filename from dual;

spool &filename

This creates correct filename "i:\Direct bank\incoming\ROSE\report_zgod_2017-08-28.csv" (I have tried to put it literally instead of &filename and the file has been created), but it still does not write a file. My guess is that's because there is a space in the path.

I have also tried

spool "&filename"
spool '&filename'

but to no avail.

I can't avoid parameter (it's used in other places in script and it can't be really calculated from SYSDATE) and I can't change the path (it has to be somewhere under "I:\Direct bank").

How can I have both the parameter and the space in spool file's name?

Solution

  • OK, I found the answer in comments to https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3581757800346555562 .

    The author of the original script has changed the defaults, so that I have to use

    spool ^filename

    instead of 

    spool &filename

    In case you have similar problem, the orginal code contained:

    set define "^"
set sqlprefix "^"

    And that was the reason I could not get the file created.