Spooling Your Output

When writing SQL scripts in a production environment it's always a good idea to spool the fruits of your labor to a log file when it is run. It also gives you the opportunity to wear your "I Told You So" teeshirt when someone denies you've run something.

Selecting the database name from sys.v_$database also gives you the warm feeling that you've run your deletion script in a development environment and not accidentally against production when you review your log file. Alternatively it could give you a more localised warm feeling down your trouser leg if you find out it's the other way round !

The set pagesize 0 command formats your SQL output better so you get one continuous page rather than giving you reams of headers.

The set timing on statement gives you timings for the SQL being run. This gives future runners of the script an idea of how long the SQL takes, which can bu useful, especially when running it in production.

whenever SQLERROR exit failure;

spool "/filename"

set echo on;
set feedback on;
set verify on;
set pagesize 0;
set termout on;
set timing on;

select name from sys.v_$database;

/************************/
/* Insert your SQL here */
/************************/

spool off;