Saving Output to a File with SPOOL

In previous lessons, you learned how to save queries as scripts and how to format SQL*Plus reports. In this lesson, you will learn how to use the SPOOL command to capture the output of those reports into an operating system file. The key goal is to understand exactly what gets written to the spool file and how environment settings influence that output.

What SPOOL Writes to a File

When SPOOL is turned on in SQL*Plus (or SQLcl), it behaves like a recorder for the SQL*Plus display. Everything that would normally appear in your SQL*Plus output window is written to the spool file, including:

1. Query results All result sets from SELECT statements, formatted according to your current COLUMN and SET settings, are captured in the file.
2. SQL*Plus feedback and messages Messages such as “14 rows selected”, “Table created”, and other SQL*Plus feedback lines are written to the spool file (unless you disable them with environment settings such as SET FEEDBACK OFF).
3. Error messages Any Oracle errors (for example, ORA- messages) and SQL*Plus errors that appear on screen are also saved. This makes spooling useful for debugging batch scripts.
4. Command echo (optional) If you enable SET ECHO ON, the text of SQL statements and SQL*Plus commands from scripts is echoed and written into the spool file along with their results. If SET ECHO OFF, the commands are not echoed—only their output appears.
5. PL/SQL output Output from PL/SQL blocks (for example, DBMS_OUTPUT.PUT_LINE) is included as long as server output is enabled (for example, SET SERVEROUTPUT ON).
6. Formatting and report layout Page titles, column headings, breaks, and computed totals defined with TTITLE, BTITLE, BREAK, COMPUTE, REPH EADER, and REPF OOTER all appear in the spool file exactly as you see them in SQL*Plus.

In short, SPOOL captures the visible output stream of SQL*Plus. It does not save your SQL statements as source code—that is the role of script files created with SAVE.

SPOOL Syntax and File Naming

You control spooling with the SPOOL command:

SPO[OL] [filename[.ext] | OFF | OUT]

• SPOOL filename Starts spooling to the specified file in the current working directory. If you omit the extension (.ext), SQL*Plus uses .lst by default (for example, SPOOL CUST_RPT creates CUST_RPT.lst).
• SPOOL OFF Stops spooling and closes the file. You can open and view or print the file with any text editor or printer tools.
• SPOOL OUT Stops spooling and sends the file to the default printer (where supported). In many modern environments, printing is handled outside of SQL*Plus, so you will more commonly use SPOOL OFF and print the file separately.

Some SQL*Plus implementations also support additional options (such as APPEND) to add to an existing file rather than overwriting it. Check your client version documentation if you need that behavior.

Example: Spooling a Customer Purchase Report

Assume you have already created a formatted report script named CUST_BUY.sql that uses COLUMN, SET, BREAK, and COMPUTE to format customer purchase information. To capture the output into a file, you might follow these steps:

1. Start spooling Begin recording everything that appears on screen:

   
   SQL> SPOOL CUST_RPT
       

   SQL*Plus starts writing all subsequent output to CUST_RPT.lst (unless you specify a different extension).
2. Run the report script Execute your report script as usual:

   
   SQL> START CUST_BUY
       

   The output might look like this on screen and in the spool file:

   
   Customer Purchasing
   Pg. 1
   
   LASTNAME                  SALES_DATE   TOTAL_SALE
   ------------------------  ----------   ----------
   Black                     12-DEC-99         61.90
                             01-MAR-00        108.03
   ************************                ----------
   sum                                       169.93
       

   This formatted report, including headings, data, and computed totals, is now captured in CUST_RPT.lst.
3. Stop spooling Once the report is complete, stop recording:

   
   SQL> SPOOL OFF
       

   At this point, the file is fully written and closed. You can open it in a text editor, archive it, attach it to an email, or print it with your preferred tools.

Controlling What Appears in the Spool File

Because SPOOL captures what appears on screen, environment settings directly affect the contents of the spool file:

• SET TERMOUT OFF Suppresses output on the screen but does not prevent it from being written to the spool file. This is useful for batch jobs where you want a clean log file but do not need interactive display.
• SET FEEDBACK OFF Removes “n rows selected” messages from the spool file, making reports cleaner.
• SET HEADING OFF Suppresses column headings in both the screen output and the spool file.
• SET ECHO ON | OFF Controls whether commands from a script are echoed into the spool file along with their results.
• SET PAGESIZE / SET LINESIZE Affects page length and line width, which determines how your report text wraps inside the spool file.

By combining SPOOL with these environment settings, you can create clean, repeatable text reports suitable for archiving, printing, or loading into other tools for further analysis.

This concludes the module’s coverage of SQL*Plus reporting. You now know how to generate formatted reports, control their layout, and save their output to files using SPOOL.