SQL Extensions   «Prev  Next»

Lesson 8Saving output to a File with SPOOL
ObjectiveDescribe what gets saved in a file when using SPOOL.

Saving Output to File with SPOOL

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.