SQL* Plus CLI  «Prev  Next»

Lesson 12Spooling and printing a report
Objective Spool a report to a file so it can be printed.

Spooling and Report Printing(spool to file)

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.


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:

  1. 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".
  2. 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.
  3. This is the SELECT statement that produces the report.
  4. SPOOL OFF causes SQL*Plus to close the output file.
  5. 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.


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 
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


[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.