sqlplus spool commands

The "spool" command is used within SQL*Plus to direct the output of any query to a server-side flat file.

sql> spool /tmp/myfile.lst

Once spool is set, SQL*Plus will continue to spool the output until the command SPOOL OFF. Note that the file cannot be seen or used until the SPOOL OFF command.

The default file extension is .lst if you dont mention while spooling.

and the default file location is current working directory if you dont mention the location of spool file.

Becuse the spool command interfaces with the OS layer, the spool command is commonly used within Oracle shell scripts.

if you spool out a file that does not exist, SQL*Plus creates a new file.  SQL*Plus replaces the existing file if the file name already exists.  In Oracle 10g, the SPOOL command has a new APPEND option.  Now, you can append new SQL*Plus output in an existing file.

  sql> spool /tmp/myfile.lst append

We can also use environment variables in spool file names.

sql> spool $ORACLE_SID.lst

Few more commands:


sql>; spool name_of_file
sql>; spool name_of_file off
sql>; spool name_of_file out
sql>; spool name_of_file create
sql>; spool name_of_file append
sql>; spool name_of_file replace