Oracle SPOOL Command

The Oracle SPOOL command is used in SQL*Plus to direct the output of any query to a server-side flat file. This is particularly useful for extracting data from the database and

1. saving it to a file for further analysis,
2. reporting, or
3. for use in other systems.

Here is an example of how to use the SPOOL command in SQL*Plus:

1. Connect to your Oracle Database using SQL*Plus.
2. Start the spooling process to a specific file on your server. In this case, we will spool to a file named "output.txt":
   

   SPOOL /path/to/your/directory/output.txt;
   

   
   Note: Replace "/path/to/your/directory/" with the actual path where you want to store the output file.
3. Execute the SQL query or queries that you want to capture the results of. For example:
   

   SELECT * FROM employees 
   WHERE department_id = 10;
   

4. Once you have executed all of your queries, turn off spooling to ensure that no additional output is written to the file:
   

   SPOOL OFF;
   

The results of the SQL query will now be written to "output.txt" in the specified directory. This file can be opened with any text editor for analysis or further use. Please note that the SPOOL command will write both the query and the results to the output file. If you only want to capture the results, ensure that you have set the necessary SQL*Plus environment settings, such as:

SET ECHO OFF;
SET FEEDBACK OFF;
SET HEADING ON;

These settings will help in formatting the output in a cleaner way and avoid capturing unnecessary information. Remember to consult the Oracle SQL*Plus documentation for your specific version of Oracle Database to ensure compatibility and to understand all available options and settings.

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