Spooling and Printing Reports in SQL*Plus

Every formatting technique covered in this module, COLUMN, BREAK, TTITLE, BTITLE, only matters once you can actually get a report out of SQL*Plus and onto paper, or into a file someone else can open. Printing a SQL*Plus report is a two-step process: first write it to a file with the SPOOL command, then hand that file to your operating system's own printing tools. This lesson covers both halves.

The core pattern for spooling a report to a file looks like this:

SPOOL filename
SET TERMOUT OFF
SELECT ...
SPOOL OFF
SET TERMOUT ON

SPOOL filename tells SQL*Plus to start copying everything it displays into the named file. If you omit an extension, SQL*Plus applies a default one, LST or LIS depending on the system. SET TERMOUT OFF suppresses the screen display while the report runs, which matters more than it sounds like for a long report: without it, you would watch potentially thousands of lines scroll past on screen while the same output writes to your file, slowing nothing down technically but making the terminal genuinely unusable in the meantime. The SELECT statement produces the actual report content. SPOOL OFF closes the output file, and SET TERMOUT ON restores normal screen display for whatever you type next.

One restriction on SET TERMOUT OFF is worth knowing precisely, since it explains a confusing moment the first time you hit it: this command only has any effect when it runs from a script. If you type SET TERMOUT OFF interactively, one command at a time, at the SQL prompt, it does nothing at all. TERMOUT suppression exists specifically for scripted, unattended report generation, not for interactive sessions.

SPOOL supports considerably more than the basic filename form shown above:

SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

SPOOL stores query results in a file, or optionally sends that file straight to a printer. file_name.ext is the name of the target file; as noted above, if you skip the extension, SQL*Plus supplies LST or LIS depending on your system, though this default extension is not appended to system files such as /dev/null or /dev/stderr.

Three keywords control exactly how SPOOL handles an existing file with the same name:

• CREATE creates a new file under the specified name.
• REPLACE overwrites the contents of an existing file, and this is the default behavior if you specify neither CREATE nor REPLACE; if the file does not already exist, REPLACE simply creates it.
• APPEND adds new content to the end of an existing file rather than overwriting it.

OFF stops spooling entirely. OUT stops spooling and sends the completed file directly to your computer's default printer, though this option is not available on every operating system. Enter SPOOL by itself, with no arguments at all, to see your current spooling status, the same no-argument convention already covered for BREAK, TTITLE, and COLUMN earlier in this module.

A few concrete examples, straight from Oracle's own documentation: SPOOL DIARY CREATE records output into a new file named DIARY. SPOOL DIARY APPEND adds to that file without erasing what is already there. SPOOL DIARY REPLACE overwrites it entirely. And SPOOL OUT stops spooling and sends the finished file to your default printer in one step.

If you are combining SPOOL with the HTML output covered in the previous lesson's SET MARKUP HTML ON, there is one caveat worth knowing before you try it: SPOOL APPEND does not parse HTML tags. If you want to build a valid HTML file using SPOOL APPEND, you need to use PROMPT, or a similar command, to write the HTML page header and footer yourself; SPOOL APPEND will not recognize or generate that structure for you automatically.

One more setting worth knowing about, mostly for completeness rather than everyday use: setting SQLPLUSCOMPATIBILITY to 9.2 or earlier disables the CREATE, APPEND, and SAVE parameters of SPOOL entirely, reverting to older, more limited spooling behavior. This exists purely for backward compatibility with very old scripts and is not something you would typically set deliberately in new work.

Once your report exists as a file, actually printing it is an operating system task, not a SQL*Plus one. On Unix and Linux systems, the specific command depends on what your distribution actually uses for printing. Many modern distributions, including Debian, Ubuntu, and Fedora, rely on CUPS, the Common Unix Printing System, rather than the older lp command, and use lpr or lpstat instead. The lp command may still exist on some systems for backward compatibility, but its availability and behavior are not guaranteed the way they once were; check your own system's documentation, or ask whoever administers it, rather than assuming lp will work as expected.

On Windows, you can send a spooled file to the printer with either the PRINT command or the COPY command:

PRINT filename

or

COPY filename LPT1:

If you use the COPY command specifically, you may notice the last page of your printout does not eject immediately. This happens because SQL*Plus does not follow the final page of output with a formfeed character, so the printer has no explicit signal telling it that page is actually finished. Some printers eventually eject the page on their own after a timeout; others will not, and you may need to press the printer's own formfeed button to force that last page out.

Getting pagination to look exactly right in a printed SQL*Plus report often takes some experimentation with PAGESIZE, but there is a more direct tool worth knowing about: SET NEWPAGE.

SET NEWPAGE 0

NEWPAGE controls what SQL*Plus actually places at each page transition. The default value is 1, producing a single blank line between pages, which is perfectly readable on screen but does nothing useful for a physical printer. Setting NEWPAGE to 0 changes this entirely: instead of a blank line, SQL*Plus inserts a genuine formfeed character at the start of every page, including the very first one, which most printers recognize directly and use to advance to a fresh sheet. Worth knowing before you set this: NEWPAGE 0 also clears the screen on most terminals, so if you run a script using this setting interactively rather than purely for printing, expect your terminal to clear itself at each page transition as a side effect.

There is a third option beyond 1 and 0 worth knowing about: SET NEWPAGE NONE suppresses both the blank line and the formfeed entirely, producing no visible separation between pages at all. This is useful in the rarer case where you want continuous, unbroken output with no page-transition markers whatsoever, for instance when spooling data meant to be parsed by another program rather than read or printed as a formatted report.

Between SPOOL for capturing output, TERMOUT for keeping a long script quiet while it runs, and NEWPAGE for controlling exactly how pages transition on paper, you now have the complete path from a formatted SQL*Plus report on screen to a physical, printed document, closing the loop on everything this module has covered about generating readable, professional reports directly from the command line.