Oracle SQL, spool to a dynamic file name

Question

I am trying to spool to a csv that contains the current_date in its name:

spool '\mydir\'||to_char(current_date,'YYYYMMDD')||'.csv';
  SELECT /*csv*/* FROM mydata;
spool off;

However, I get an error:

SP2-0768: Illegal SPOOL command
Usage: SPOOL { <file> | OFF | OUT }
where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

Is it not allowed? Is there a workaround?

The same error appears if I try using a substitution variable, which does not seem to be allowed either.

Use a variable, as detailed in [this](https://asktom.oracle.com/pls/apex/asktom.search?tag=dynamic-spool-file-name) _Ask Tom_ question. — Abra, Mar 30 '20 at 19:15

score 0 · Answer 1 · answered Mar 30 '20 at 19:30

Here's an example of how I do it [you may need to fiddle around a bit (e.g. set heading off) to get it exactly how you want it]:

set echo off 
set feedback off
set term off verify off head on
set linesize 200 pagesize 9999
set long 4000000
set serveroutput on

-- get instance name in file name
column DATE_YYYYMMMDDD new_val FILE_NAME noprint

Select to_char(sysdate, 'YYYYMMDD') DATE_YYYYMMMDDD from  dual;


spool \temp\CSV_&FILE_NAME..CSV

select table_name || ',' || column_name from user_tab_columns; 

spool off
set serverout off

score 0 · Answer 2 · answered Mar 30 '20 at 19:33

col myspoolbase noprint new_value val_myspoolbase
select 'myfilename'  myspoolbase from dual;

col log_date noprint new_value val_log_date
select to_char(sysdate,'yyyymmdd_hh24miss') log_date from sys.dual;
spool &&val_myspoolbase._&&val_log_date..log

select sysdate from dual;
spool off

Oracle SQL, spool to a dynamic file name

2 Answers2