Saving Output to File with SPOOL

When using the SPOOL command in SQL*Plus with Oracle 12c, the output that gets saved into a file includes the results of SQL queries and the SQL*Plus command-line feedback. Specifically, here's what you can expect to be saved:

1. Query Results: The primary content saved is the output of any SQL queries executed between the `SPOOL ON` and `SPOOL OFF` commands. This includes data retrieved from tables, views, or any other database objects that are queried.
2. SQL*Plus Command Output: Any feedback from SQL*Plus commands that are executed while spooling is active will also be included in the file. This might include messages indicating the success or failure of commands, row counts from DML operations (like INSERT, UPDATE, DELETE), and any other system messages generated by SQL*Plus.
3. Formatting and Headers: The file will also capture the formatting of the output as set by SQL*Plus commands. For instance, column width, titles, and formatting options set using the `SET` command in SQL*Plus will be reflected in the spooled file.
4. Error Messages: If any errors occur during the execution of SQL statements or commands, these error messages will also be captured in the spooled file. This is particularly useful for debugging purposes.
5. PL/SQL Output: If PL/SQL blocks (anonymous blocks, stored procedures, functions, etc.) are executed, their output, including DBMS_OUTPUT content, will be saved in the spool file, provided the server output has been enabled in SQL*Plus.
6. Command Echoing: If the `SET ECHO ON` command is used, the SQL commands and scripts run will also be echoed and saved in the spooled file along with their results. This is useful for understanding the context of the output in the file.

It's important to note that the exact content of the spooled file can be influenced by various `SET` commands in SQL*Plus, such as `SET FEEDBACK`, `SET HEADING`, `SET ECHO`, and others. These commands allow for control over what information is included in the spool file and how it is formatted.

You have already discovered and practiced how to save a query into a file. This lesson shows you how to save the results of executing your query into a file by using the SPOOL command. Keep in mind that once the spooling has begun, everything displayed on your screen is saved to the file. The syntax of the command is:

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

If you leave off the .ext portion of the filename, SQL*Plus uses .lst. After starting spooling, there are two ways to stop it:

SPOOL OFF 

(stop spooling) and

SPOOL OUT 

(stop spooling and print the file to the default printer).
Here is an example. Let us say you have prepared a file named CUST_BUY.sql with a query and some SQL*Plus environment commands. You want to write the report to a file and print it. The first step is to type this command while logged into SQL*Plus:

SPOOL CUST_RPT

Next, you execute the SQL script file:

START CUST_BUY

SQL*Plus executes the file, displaying the results on the screen and writing them to the spool file:

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

After completing the report, you stop the spooling and print the file:

SPOOL OUT

Use SET TERMOUT OFF to suppress output displayed on the screen. This does not affect the output that you spool.
The next lesson concludes this module.