Square DBA – Oracle DBA Talk

Dynamically set the name of a spool file in SQL*Plus

Do you need to dynamically create a spool filename in SQL*Plus? You may have a need to set the name of a spool file based on the current time or possibly the database you are connected to. It’s often needed when scripts is run as scheduled job and there is a need to generate files with different names. Much of the time I do this in a shell script, but I do run across an occasion to do it SQL*Plus. Here is a solution for using date:

define logname=date
column clogname new_value logname
select 'prefix_'||to_char(sysdate, 'yyyymmdd') clogname from dual;
spool '&logname'

What about including the database name? Here is one I used today the uses the current date, database name, and a random number.

define logname=date
column clogname new_value logname
select substr(global_name,1,
 decode(dot,0,length(global_name),dot-1))||'-'||
 to_char(sysdate, 'yyyymmddhh24miss')||'-'||
round(dbms_random.value(10,99),0) clogname
from (select global_name, instr(global_name, '.')
dot from global_name);
spool c:\sqlpluslogs\&logname..txt