Append date to file name for automated jobs
This Oracle script will automatically add the date to a flat file name.
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.