Skip to Main Content
  • Questions

  • How to use spool command to save sql script resultset to a file ,and filename include 'date format' ?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Quanwen.

Asked: July 10, 2017 - 8:31 am UTC

Last updated: July 11, 2017 - 11:45 am UTC

Version: 11.2.0.4.0

Viewed 100K+ times! This question is

You Asked

Hi,
my sql script file is,
SET heading       ON
SET echo            OFF
SET feedback     OFF
SET trimspool     ON
SET newpage     NONE
SET verify           OFF
SET define          OFF
SET termout        OFF
SET timing          OFF
 
SET linesize    400
SET pagesize  0
 
SPOOL check_redo_apply_xxxxxxxxxxxxxx.log
 
COL current_scn FOR 9999999999999999
 
SELECT current_scn FROM v$database;
 
SELECT MAX(sequence#) FROM v$archived_log WHERE applied='YES';
 
SELECT MAX(sequence#) FROM v$archived_log WHERE applied='NO';
 
SELECT process,pid,status,sequence#,block#,blocks,delay_mins FROM v$managed_standby;
 
SELECT sequence#,status,archived FROM v$standby_log;
 
COL name                   FOR  a24
COL value                   FOR  a12
COL unit                      FOR  a28
COL time_computed   FOR  a20
COL datum_time         FOR  a20
 
SELECT * FROM v$dataguard_stats;
 
SPOOL OFF


please ask 'SPOOL check_redo_apply_xxxxxxxxxxxxxx.log' in 'xxxxxxxxxxxxxx',i wanna to use a date format, how to add it in SQLPLUS ?

Thanks.

Regards

QuanwenZhao

and Chris said...

What precisely are you trying to do? Generate the filename with a dynamic date?

If so you can use a substitution variable. Define a column as the variable with new_value. Then select a date into it with your desired format mask:

SQL> column dt new_value filedt
SQL> select to_char(sysdate,'YYYYMMDD') dt from dual;

DT
--------
20170710

SQL> spool &filedt..log
SQL> spool off
SQL> ho dir *.log
 Volume in drive C is System
 Volume Serial Number is E6AB-58D8

 Directory of C:\temp

10/07/2017  10:45                16 20170710.log
               1 File(s)             16 bytes
               0 Dir(s)  149,895,012,352 bytes free


HT: Chris Jones https://blogs.oracle.com/opal/sqlplus-101-substitution-variables#9_5

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

not add date format to filename

Quanwen Zhao, July 11, 2017 - 3:05 am UTC

Hi, Mr Chris Saxon,
I add your code segment to my sql script file,
SET heading      ON
SET echo         OFF
SET feedback     OFF
SET trimspool    ON
SET newpage      NONE
SET verify       OFF
SET define       OFF
SET termout      OFF
SET timing       OFF

SET linesize 400
SET pagesize 0

COL today_date NOPRINT NEW_VALUE td_date

SELECT TO_CHAR(SYSDATE,'yyyymmddhh24miss') td_date FROM DUAL;

SPOOL check_redo_apply_.&td_date..log


and I generate a file that has no added date format.

[oracle@orcl26 script]$ ls
check_redo_apply_.&td_date..log

Chris Saxon
July 11, 2017 - 11:45 am UTC

You need to

set define on

A reader, July 11, 2017 - 8:47 am UTC

Quanwen,

you have to use today_date in the select:

SELECT TO_CHAR(SYSDATE,'yyyymmddhh24miss') today_date FROM DUAL;


Quanwen Zhao, July 13, 2017 - 3:50 am UTC

Exactly,'set define on' is ok.

Thanks you very much!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.