Tip

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

    Requires Free Membership to View

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 tdichiara@techtarget.com 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.

This was first published in January 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.