SQLPLUS : DYNAMIC NAME FOR A SPOOL FILE

The post provides a way to change the spool file name dynamically during a SQL script execution.

GOAL

I am running a SQL script and want to produce a spool file with a date appended to its name.

SOLUTION

You can achieve it by using a COLUMN VARIABLE in the SQL*Plus. See for more details on NEW_VALUE.

NEW_V[ALUE] variable - Specifies a variable to hold a column value.

NOPRI[NT] | PRI[NT] - Controls the printing of the column (the column heading and all the selected values).

For instance, in the following example, I create a substitution variable SPOOL_FILE through the NEW_VALUE keyword, and then I store a query result in that variable.

-- Specify a new variable for the "FILE_NAME" column. 
-- A variable name is "SPOOL_FILE".

SQL> COLUMN FILE_NAME NEW_VALUE SPOOL_FILE NOPRINT

-- Generate a new name for a spool file by simple query.
-- The result of "FILE_NAME" column will be automatically 
-- stored in the "SPOOL_FILE" variable

SQL> SELECT UPPER(INSTANCE_NAME) || '_' || 
     TO_CHAR(SYSDATE,'DDMONYYYY_HH24MISS') || '.out' FILE_NAME 
     FROM V$INSTANCE;

-- Print the content of the "SPOOL_FILE" variable

SQL> PROMPT &SPOOL_FILE

TST19C_07NOV2021_132852.out
 
-- Start spooling

SQL> SPOOL &SPOOL_FILE

-- Query current date from a database

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
------------------
07-NOV-21

-- Stop spooling

SQL> SPOOL OFF

Now let’s see if the spool file has been created and what name it has.

SQL> !ls
TST19C_07NOV2021_132852.out

SQL> !cat TST19C_07NOV2021_132852.out
SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
------------------
07-NOV-21

SQL> SPOOL OFF

That’s it. The file has the dynamically generated name.

REFERENCES

SQL*Plus Substitution Variables - DEFINE variables and parameters in SQL Queries