René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

spool [SQL*PLUS]

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
The three modifiers create, replace and append are improvements that come with Oracle 10g.
See also trimspool.

Suppressing output to the console while spooling

When a script is run that spools some output to a file, it might not be desirable to also print to the console what goes into the file anyway. This can be achieved by set echo off and set termout off.

Restriction level

The spool command won't work if the restriction level is set to at least 2: 
C:>sqlplus -r 2 rene/rene

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 17 21:10:39 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

RENE> spool blabla
SP2-0738: Restricted command "spool" not available
RENE>

Adding the current date to the file name

column date_column new_value today_var
select to_char(sysdate,'yyyy-mm-dd') date_column
from dual
/
spool file_of_&today_var

-- some statements here

spool off
This will create a file such as file_of_2005-05-17.lst.

Thanks

Thanks to Janus Christensen who spotted an error on this page and corrected it.