Generating dynamic spool files in SQL Plus

The following example will append YYYYMMDD_HHMISS to the spool file name.

set echo on
column spool_file new_val spool_file;
select 'dynamic_spool_file_' 
       || to_char(sysdate, 'yyyymmdd_hh24miss' ) spool_file 
from dual;
spool &spool_file
select 'file name : &spool_file' as spool_file_name from dual;
set echo off
spool off