Spooling Query Result

Occasionally, you will be asked to send the query result back to the requester. If the result set fits in a page, that is great, copy the result and send the result back. How do you manage, if the query result does not fit into a page and has thousands of lines of output. Oracle and most DBMS system will let you write a query on a text file. The process of writing query result to a file is called a spooling. With this article, I will show you how you can spool a result into a file for Oracle database.

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

SQL Developer:

1. Turn of the Spool
2. Run the Query
3. Turn off the Spool

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

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

SQL Plus:

The approach is similar to that of SQL Developer except you can run one command at a time, it does not have to be in a single transaction.

1. Turn of the Spool
2. Run your Query
3. Turn off the Spool

Syntax:
The result will be spooled to OS directory of database server. You can spool to a directory but you will need to have a proper write access to the directory.

-- Turn on the spool
    spool spool_file.txt
 -- Run your Query
    select  * from dba_tables;
 -- Turn of spooling
    spool off;

What if your Oracle is installed on Linux and you want the result to your PC so that you can email it to the requester. You can email directly from Linux using mail package or you can telnet from your windows to your Linux and copy to your windows.