Oracle SPOOL Command

The `SPOOL` command in SQL*Plus is used to direct output to a file. Below is an example of how to use it:
Example: Using `SPOOL` in SQL*Plus

-- Start SQL*Plus and connect to Oracle
sqlplus username/password@database

-- Set up the environment
SET LINESIZE 200
SET PAGESIZE 100
SET ECHO OFF
SET FEEDBACK OFF

-- Start spooling to a file
SPOOL /tmp/query_output.txt

-- Execute a query
SELECT employee_id, first_name, last_name, salary 
FROM employees
WHERE department_id = 10;

-- Stop spooling
SPOOL OFF

-- Exit SQL*Plus
EXIT;

Explanation:

1. SET LINESIZE 200 → Sets the line width to 200 characters to prevent text wrapping.
2. SET PAGESIZE 100 → Controls the number of rows per page (helps format output).
3. SET ECHO OFF → Prevents SQL commands from being displayed in the output file.
4. SET FEEDBACK OFF → Hides query feedback like "X rows selected".
5. SPOOL /tmp/query_output.txt → Starts writing output to the specified file (/tmp/query_output.txt on Linux/macOS or C:\output.txt on Windows).
6. Executes a SELECT query to fetch employee details.
7. SPOOL OFF → Stops writing output to the file.
8. EXIT; → Exits the SQL*Plus session.

This method is commonly used for exporting SQL query results to a text file for further processing or reporting.

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:

1. Turn ON the Spool
2. Run the Query
3. 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; 

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.

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:

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