SPOOLING TO A DYNAMICALLY GENERATED SPOOL FILE NAME

 
QUESTIONS

 
REQUEST

 
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