SPOOL stores query results in a file, or optionally sends the file to a printer.
SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]
With the spool system variable, you can see the SPOOL information (status and file)
show spool
spool OFF
trimspool will suppress the spaces.
One of the first usage of the SPOOL keyword is to create a file with DDL statement through a select statement and run it afterwards.
From SQL Developer, when running the script (F5), you need to run it from an other SqlWorksheet to get the correct output because all SQLPlus command are not implemented
-- Example
@@myScript.sql
Set heading off;
set feedback off;
set echo off;
Set lines 9999;
Spool compile_invalid.sql
select
'alter ' || OBJECT_TYPE || ' ' || owner || '.' || object_name || ' compile;'
from
dba_objects
where
status = 'INVALID'
and
object_type in ('PACKAGE','FUNCTION','PROCEDURE','VIEW')
;
spool off;
set heading on;
set feedback on;
set echo on;
@compile_invalid.sql