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

Question

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?

Answer 1

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.

Answer 2

When the path has a space in it then try using the short form of the path name. So, to SPOOL to /opt/oracle/oradata/Custom Scripts/orders_between_dates.txt; it's...

SQL> SPOOL /opt/oracle/oradata/Custom~1/orders_between_dates.txt;

If you're running it from a .sql script then put "" around it, like this...

SET FEEDBACK OFF;
SET MARKUP CSV ON;
SET VERIFY OFF;
SPOOL "/opt/oracle/oradata/Custom~1/orders_between_dates.txt"
SELECT * FROM orders_detail WHERE order_date BETWEEN '&1' AND '&2';
SPOOL OFF;
EXIT;

The command to run this script for a rolling seven day window would be something like...

> sqlplus ot/Orcl1234@xepdb1 @"/opt/oracle/oradata/Custom Scripts/orders_between_dates.sql" $(date +%d-%b-%Y -d '-7 days') $(date +%d-%b-%Y)