Friday, December 26, 2014

Automated spool file name generation

There was requirement recently, where spool file of SQLPlus has to be generated with an automatic name that consists of database name with timestamp (for example, db001_2014-12-26_04-54-13.log). Here is the script that generates spool file name dynamically.

set termout off
set feedback off
undefine spoolfile
column spoolfile new_value spoolfile noprint
select sys_context('userenv', 'db_name') || '_' || to_char(sysdate, 'YYYY-MM-DD_HH24-MI-SS') || '.log' as spoolfile from dual;
set termout on
set feedback on

set echo on
spool &spoolfile

select sysdate from dual;

spool off

No comments:

Post a Comment