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.
SPOOL command
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 diagram illustrates the use of these two commands:
SPOOL filename: 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"."
SET TERMOUT OFF: 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.
SELECT ...: This is the SELECT statement that produces the report.
SPOOL OFF: SPOOL OFF causes SQL*Plus to close the output file.
Stores query results in a file, or optionally sends the file to a printer.
Terms:
file_name[.ext]
Represents the name of the file to which you wish to spool. SPOOL followed by file_name begins spooling displayed output to the named file.
If you do not specify an extension, SPOOL uses a default extension (LST or LIS on most systems). The extension is not appended to system files such as /dev/null and /dev/stderr.
CRE[ATE]
Creates a new file with the name specified.
REP[LACE]
Replaces the contents of an existing file. If the file does not exist, REPLACE creates the file. This is the default behavior.
APP[END]
Adds the contents of the buffer to the end of the file you specify.
OFF: Stops spooling.
OUT: Stops spooling and sends the file to your computer's standard (default) printer. This option is not available on some operating systems.
Enter SPOOL with no clauses to list the current spooling status
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 on Unix Systems
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
Use the the "lp command" to send a file to the printer on most Unix systems.
The answer to whether you can use the `lp` command to send a file to the printer on most Unix systems depends on a few factors:
System Distribution:
While the `lp` command used to be widespread in Unix-like systems, its usage has declined in recent years. Some popular distributions like Debian, Ubuntu, and Fedora no longer use it by default.
These distributions typically use the **CUPS (Common Unix Printing System)** for printing, which utilizes the `lpstat` and `lpr` commands instead.
Specific System Configuration:
Even on systems with CUPS, the `lp` command might be installed as an alternative or for backward compatibility.
However, its functionality might be limited or not actively maintained.
Printer Configuration:
The `lp` command needs to be configured to know about the specific printer you want to use.
This configuration might not be present on all systems, especially if CUPS is the primary printing system.
Therefore, while the `lp` command might exist on some Unix systems, it's not a guaranteed option. It's recommended to:
Check your system documentation for the preferred printing commands: Look for commands like `lpr`, `lpstat`, or `cupslp`.
Use the appropriate command based on your system's configuration.
Consult your system administrator if you're unsure or encounter problems.
Remember, relying on outdated commands like `lp` can lead to compatibility issues and security vulnerabilities. Using the recommended commands for your specific system ensures a smoother and more secure printing experience.
On Windows you can use either the PRINT command or the COPY command, as shown here:
PRINT filename
or
COPY filename LPT1:
COPY command
If you use the COPY command, you may find that the last page is not 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 on SQL*Plus report
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.
