Wednesday, October 02, 2013

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

No comments: