To print a SQL*Plus report, you have to write it to a file and send the file to a printer.
Use the SPOOL command to write the report to a file and then use your operating system commands to print that file.
Spooling
You can use the SPOOL command to write a report to a file in SQL*Plus just as you can in Server Manager.
By default, SQL*Plus will scroll the report across the screen as you spool it to a file. You can prevent that by using the SET TERMOUT OFF command. The SET TERMOUT OFF command is especially useful when the report is long.
The following mouseover illustrates the use of these two commands:
The SPOOL command causes SQL*Plus to start copying its output to the specified file. The extension, if you do not supply one, will usually be ".lis" or ".lst".
Turns the terminal output off so that you won't see the report scroll by on the screen. This command only works if it is executed from a script. If you are executing commands interactively, it has no effect.
This is the SELECT statement that produces the report.
SPOOL OFF causes SQL*Plus to close the output file.
Reinstates terminal output.
Oracle Spool Command
For the SET TERMOUT OFF command to work, you must be executing a script file. If you are just typing commands into SQL*Plus one at a time, then SET TERMOUT OFF will have no effect.
Printing
Once you have your report in a file, you can copy that file to a printer.
On most Unix systems, the lp command may be used to send a file to the printer. The syntax is simple and looks like this:
lp filename
On Windows NT, you can use either the PRINT command or the COPY command, as shown here:
PRINT filename
or
COPY filename LPT1:
If you use the COPY command, you may find that the last page isn't immediately ejected from your printer.
That's because SQL*Plus does not follow the last page with a formfeed character[1], so the printer does not know that the page is done.
Some printers will eventually eject the page, and some will not.
If you have one of the latter, you may need to press the formfeed button on your printer to force the last page to print
Pagination
Getting the pagination on a SQL*Plus report to work out just right sometimes takes a bit of work. You may have to experiment with several PAGESIZE settings before you find one that works the way you want it to. Alternatively, you can try placing this command in your report script:
SET NEWPAGE 0
The NEWPAGE setting controls the number of lines that SQL*Plus prints to mark each page-break. The default setting is 1, resulting in one blank line between pages. Setting NEWPAGE to zero causes SQL*Plus to place a formfeed character between pages instead of a blank line. This makes pagination much easier because most printers recognize the formfeed character and advance to a new page.
[1]formfeed character: A special character used to tell a printer to advance to a new page. Formfeed characters were a standard item in the days before page description languages such as Postscript.