SPOOL...APPEND

According to the book I found this in, it was new in Oracle 10 but I tested it on Oracle 11.2.0.2.7. You can SPOOL output to a file like this…

 

SQL> spool spool_test

SQL> select 'Andrew' Andrew from dual

  2  /

 

ANDREW

------

Andrew

 

SQL> spool off

 

…then you can run an OS command from within SQL*Plus to display the contents of the SPOOL file:

 

SQL> !cat spool_test.lst

SQL> select 'Andrew' Andrew from dual

  2  /

 

ANDREW

------

Andrew

 

SQL> spool off

 

If you repeat the SPOOL command and run some more SQL…

 

SQL> spool spool_test

SQL> select 'Brian' Brian from dual

  2  /

 

BRIAN

-----

Brian

 

SQL> spool off

 

… the original contents of the output file will be overwritten:

 

SQL> !cat spool_test.lst

SQL> select 'Brian' Brian from dual

  2  /

 

BRIAN

-----

Brian

 

SQL> spool off

 

But if you add the word APPEND to the end of the SPOOL command and run some more SQL…

 

SQL> spool spool_test append

SQL> select 'Colin' Colin from dual

  2  /

 

COLIN

-----

Colin

 

SQL> spool off

 

…the new output is added to the end of the output file:

 

SQL> !cat spool_test.lst

SQL> select 'Brian' Brian from dual

  2  /

 

BRIAN

-----

Brian

 

SQL> spool off

SQL> select 'Colin' Colin from dual

  2  /

 

COLIN

-----

Colin

 

SQL> spool off