Append date to file name for automated jobs

You may be sometimes required to create flat files on a weekly or monthly basis where the name of the file must be based on the date it was created. The following script will automatically add the date to the file name. This tip has been tested on Oracle versions 6, 7 & 8i.

set pagesize 1800 linesize 450 
REM 
REM    Author    : Rohit Sinha 
REM    DATE      : 05/30/98 
REM    File Name  : sql_dated_spool_name.sql 
REM    Usage      : On sqlplus prompt enter: 
REM                    @sql_dated_spool_name 
REM    Description: Creates a variable file name from Oracle date functions 
REM                  to which the output from SQL*Plus script is spooled. 
REM 
set heading off feedback off verify off pause off 
REM column year newline 
REM 
REM    Create temporary file to define MONTH and YEAR variables. 
REM 
spool tempfile.sql 
select 'define MONTHDAYYEAR = '||to_char(sysdate,'MONDDYYYY') from dual; 
spool off 
set heading on  feedback on  verify on 
REM 
REM    Start the temporary file so that definition can be used in the 
REM    spool command below. 
REM 
start tempfile.sql 
REM 
REM the "weeklyreport" suffix can be replaced with any desired prefix 
spool  /home/rdwp/voyagert/TERRMOVE/weeklyreport&MONTHDAYYEAR..txt 
REM the script for the spool file goes here. The following is used 
REM as test. Basically one need to replace REM the "select sysdate 
REM from dual" with the required command.

select sysdate from dual; 
spool off 
host rm tempfile.sql 
exit; 
---------------------------------------------- 

Reader feedback

Geoff H. writes: There are alternate ways to timestamp the date and time on the filename (there would be, wouldn't there!). For SQL scripts/jobs that are run periodically, I generate my SQL scripts dynamically using Unix shell scripts. The advantage here is that you can utilise all the variables available in Unix simply; i.e., a spool date would be:

SPOOL_DATE=`date +"%d_%m_%Y_%T"`

When building your SQL script, append it to the filename:

cat > ${TMP_DIR}/${PID}.${COMMAND}.script1.sql << EOF1
spool  ${WORK_DIRECTORY}/${COMMAND}.script1.${SPOOL_DATE}

your commands, etc

spool off
EOF

Then run the file.

Leo W. writes: To spool a file with date stamps should be much easier than this. The following is what I use to get the same result.

set head off feedback off echo off term off verify off
set pagesize 0
set linesize 1000
set trimspool on
col date_stp new_value date_stp noprint
select to_char(sysdate,'yyyy_mm_dd') date_stp from dual;

spool file_name_&date_stp..txt

your query;

spool off

For More Information

• What do you think about this tip? E-mail the Editor at [email protected] with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.