Print Friendly, PDF & Email

 
QUESTIONS

How to create a spool file with the date appended to file name?
Is it possible to change the spool file name dynamically?

 
REQUEST

I want to produce a spool file with a name generated during runtime.

 
SOLUTION

Create a substitution variable (NEW_VALUE SPOOL_FILE) and store the query result in it

SQL> COLUMN SPOOL_FILE NEW_VALUE SPOOL_FILE NOPRINT
SQL> SELECT UPPER(INSTANCE_NAME) || '_' || HOST_NAME || '_' || TO_CHAR(SYSDATE,'DDMONYYYY_HH24MISS') || '.txt' SPOOL_FILE FROM V$INSTANCE;
SQL> PROMPT &SPOOL_FILE

DBPILOT_dbpilot.net_19JAN2018_140848.txt
 
SQL> SPOOL &SPOOL_FILE
SQL> SELECT SYSDATE FROM DUAL;
SQL> SPOOL OFF

Confirm that the file is created and contains the query output

SQL> !ls
DBPILOT_dbpilot.net_19JAN2018_140848.txt

SQL> !cat DBPILOT_dbpilot.net_19JAN2018_140848.txt
SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
------------------
19-JAN-18

SQL> SPOOL OFF

 
REFERENCES

Oracle SQL*Plus Substitution Variables
How to store database columns as variables in SQL*Plus

 
 

Version  : 14:12 19.01.2018
Database : Oracle database 10G – 12cR2