:

Spool on in SQL Plus

Spool on in SQL Plus

Why?

When you run a query which could potentially generate a large number of results, or just if you want a record/file of the results, you can use the SPOOL command. Spooling results from a SQL query to a file is very simple, and you can even append to an existing file in Oracle 10g and later.

How?

To start spooling use the SPOOL filename.log command. This will output everything to the file after you issue the command. The output doesn’t necessarily need to be presented on screen. You can stop this from happening by using the SET TERMOUT OFF command in your SQL script. Issuing the SET TERMOUT OFF command wil not work directly from the command line, it will only be of use if you are calling a script from SQL Plus.

Spool Append in 10g or Later?

If you are using a 10g or later database you can also append to an existing file. The APPEND keyword is specified after the filename part of the statement, for example:

SPOOL filename.log APPEND

The above will write out to an existing file called filename.log. If the file does not already exist then it will be created.

SQLPLUs to File

The spool file that you create here doesn’t have to have a .log extension, you can use any file format. You could write a script to generate SQL from SQL and concatenate the output together so that there are commas in between each value to create a CSV file. Here is a quick example:

SELECT sid||','||serial#
FROM v$session;

SPOOL OFF

To stop spooling to a file you just issue the command SPOOL OFF. It’s that simple.

SPOOL Tips

A few ideas and suggestions that I would make to you when you are looking to turn spool on in sqlplus.

    1. SET LINESIZE

Make sure that you set the linesize large enough to accommodate all of the output for one row to a single line. There is nothing worse than having the data span multiple lines. It looks ugly and is really hard to read. I’d recommend setting a large LINESIZE and using the TRIMSPOOL command, detailed below, to trim your output.

    1. SET TRIMSPOOL ON

This is a great command. It allows you to have a large linesize if needed but if the row you output is shorter than the linesize it removes any blank lines instead of padding them out with spaces. Great for keeping the file as small as possible.

    1. COLUMN Sizes

Use the COL colname FORMAT Asize commands to format the sizes of your character and numeric columns so they are readable. For example, for a VARCHAR2(50) column called Name and a NUMBER(6) column called ID I would use the following:

COL NAME FOR A50
COL ID FOR 999999

This will mean that every piece of data from the row will fit on one line and look much better.

    1. SET PAGESIZE

I always set the pagesize either really large or to 0. The PAGESIZE command controls how often the headers for the column names will be displayed throughout the output. If you don’t want them displayed anywhere in the output apart from at the top set the PAGESIZE to 0.

As a rough guideline, I look at the size of each column that I am selecting and then make the linesize larger than the total. You know that each row will then fit on each line in the output file that way.

    1. SET ECHO OFF

Using the SET ECHO command in your SQL Plus environment will allow you to control whether or not the SQL commands you execute are present in spool file.

Hopefully that gives you a little more information about how to turn spool on in sqlplus and some tips for making the data output to the file as readable as possible.

Like it, share it...

Category: SQL Plus


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *