Search Oracle Blogs

List of Blogs maintained by Paweł Barut.

Monday, June 11, 2007

Dynamically set name of spool file in SQL*Plus

Small tip for today. Often there is a need to set name of spool file based on current time and might by also database you are connected to. It's often needed when script is run as scheduled task and there is a need to generate files with different names. So here is an solution for using date:
define logname=date
column clogname new_value logname
select 'prefix_'||to_char(sysdate, 'yyyymmdd') clogname from dual;
spool '&logname'
If there is a need to have also name of database included into filename then it can be read from global_name view:
define logname=date
column clogname new_value logname
select 'prefix_'||substr(global_name,1,
 to_char(sysdate, 'yyyymmdd') clogname
from (select global_name, instr(global_name, '.') dot from global_name);
spool '&logname'
Hope you find it useful.
I would also like to share an photo I made in Rome in February this year
Rome, Colosseum, Feb 2007
To see more pictures you are welcome to follow Rome 1, Rome 2, Rome 3

Cheers, Paweł


Anonymous said...

Another useful tip is that "." is the variable delimiter, so &var1..log appends ".log" to variable name.

Anonymous said...


Thats great this is the one i was looking for.

Ricardo said...

Gracias, era loq estaba buscando!!!

Ricardo said...

Gracias. Estaba buscando esta solucion hace tiempo!!! Thanks!

Anonymous said...

Exactly what I was looking for... Thanks a million Mr. Barut.
By the way, the photographs you took of Rome are just too good...


Anonymous said...

It's trick and useful!


Anonymous said...

col dt1 noprint new_value dt1

SELECT TO_CHAR (SYSDATE, 'yyyymmdd') || '.log' dt1 FROM DUAL;

spool C:\log_file_&dt1

sql commands

spool off


Copyright © Paweł Barut
Printing from DOS to USB Printer