Writing sqlplus output to a file

Question

You may use the SPOOL command to write the information to a file.

Before executing any command type the following:

SPOOL <output file path>

All commands output following will be written to the output file.

To stop command output writing type

SPOOL OFF

Also note that the SPOOL output is driven by a few SQLPlus settings:

• SET LINESIZE nn - maximum line width; if the output is longer it will wrap to display the contents of each result row.

• SET TRIMSPOOL OFF|ON - if set OFF (the default), every output line will be padded to LINESIZE. If set ON, every output line will be trimmed.

• SET PAGESIZE nn - number of lines to output for each repetition of the header. If set to zero, no header is output; just the detail.

Those are the biggies, but there are some others to consider if you just want the output without all the SQLPlus chatter.

Make sure you have the access to the directory you are trying to spool. I tried to spool to root and it did not created the file (e.g c:\test.txt). You can check where you are spooling by issuing spool command.

just to save my own deductions from all this is (for saving DBMS_OUTPUT output on the client, using sqlplus):

• no matter if i use Toad/with polling or sqlplus, for a long running script with occasional dbms_output.put_line commands, i will get the output in the end of the script execution
• set serveroutput on; and dbms_output.enable(); should be present in the script
• to save the output SPOOL command was not enough to get the DBMS_OUTPUT lines printed to a file - had to use the usual > windows CMD redirection. the passwords etc. can be given to the empty prompt, after invoking sqlplus. also the "/" directives and the "exit;" command should be put either inside the script, or given interactively as the password above (unless it is specified during the invocation of sqlplus)