Lesson 11	The SPOOL Command
Objective	Use SQL Developer or SQL PLus for SPOOL command.

Oracle SPOOL Command

You can spool using SQL Developer or with SQL PLus. Each approach is little different even though the concept is the same.

Occasionally, you will be asked to send the query result back to the requester. If the result set fits in a page, you will be able to copy the result and send the result back. On the other hand, if the query result does not fit into a page and is greater than the size of the page you will need to the use the SPOOL command.
Oracle will let you write a query result on a text file. The process of writing a query result to a file is called spooling.
Question: How you can spool a result into a file for Oracle database.

You can spool using SQL Developer or SQL PLus. Each approach is a little different even though the concept is the same.
With SQL Developer:

Turn ON the Spool
Run the Query
Turn OFF the Spool

Syntax: The command below only works if you run the script using SQL Developer. To do this, highlight all three commands, right click and run as script
.

-- Turn on the spool 
    spool c:\users\ggould\spool_file.txt 
 -- Run your Query 
    select  * from dba_tables; 
 -- Turn of spooling 
    spool off;

Oracle Database Administration

The SPOOL command is used to echo the output to a text file. Spooling output to a file is a good thing to do whenever you run a script, or any other command that produces a large output. It enables you to review the output later, without having to worry about it scrolling off the top of the screen. To begin writing output to a file, use the SPOOL command followed by the name of the file to which you want to write the output. You can specify the full directory path of the file if you like. The following examples will work on Windows NT and UNIX respectively:

SPOOL c:\output.txt
SPOOL $HOME/output.txt

All the output will now be written to a file named output.txt in the root directory of your C drive (Windows), or to your home directory (UNIX). To stop spooling, use the following command:

SPOOL OFF

I use the SPOOL command whenever I run a critical script, or whenever I execute a critical set of commands, because it gives me a record that I can turn to later if I find out that something did not go as expected.

Spooling Results

Many times you will find it helpful to spool the results of you session. We find it especially helpful to capture the output of the show parameters command; you can then use this output for your first attempt at customizing an initialization parameter file. By using the captured output as a starting point, you eliminate typing errors.

Note: A misspelled entry in the initialization parameter file will prevent the database from starting up.
To start recording your actions while in SQL*Plus, do the following:

Enter the command spool file_name where the filename conforms to the rules of your computer.
Oracle redisplays the prompt after the spool file is opened.
Go about your job in SQL*Plus, and then close the file by entering the spool off command; Oracle closes the output file and redisplays the Server Manager prompt.

Spool Command - Exercise

Click on the link below to attempt the [Spool Command Exercise].
Spool Command - Exercise