SQL Plus - SPOOL Command

Card Puncher Data Processing


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
Set heading off;
set feedback off;
set echo off;
Set lines 9999;

Spool compile_invalid.sql

   'alter ' || OBJECT_TYPE || ' ' || owner || '.' || object_name || ' compile;'
   status = 'INVALID'
   object_type in ('PACKAGE','FUNCTION','PROCEDURE','VIEW')

spool off;

set heading on;
set feedback on;
set echo on;


Discover More
Card Puncher Data Processing
SQL Plus - Command Reference

Commands Description @ @@ / ACCEPT APPEND ARCHIVE ATTRIBUTE BREAK BTITLE CHANGE CLEAR COLUMN COMPUTE CONNECT COPY Copies data from a query to a table in the same or another...
Card Puncher Data Processing
SQL Plus - Csv Export

How to export a CSV with SQL Plus. Export of the tables all_objects where:
Card Puncher Data Processing
SQL Plus - Show (System Variable) Command

The SHOW command shows the value of: a SQLPlus system variable or the current SQLPlus environment. SHOW SGA requires a DBA privileged login. where option represents one of the following: ...
Card Puncher Data Processing

Termout is a system variable that controls the display of output in the console generated by commandsexecuted from a script. If termout is off, no output will be send to the console (included the result...
Card Puncher Data Processing
SQLPlus - Trimspool

Trimspool suppress the whitespace of the spool

Share this page:
Follow us:
Task Runner