Storing and Printing Query Results

Send your query results to a file when you want to edit them with a word processor before printing or include them in a letter, memo, or other document.

To store the results of a query in a file--and still display them on the screen--enter the SPOOL command in the following form:

SPOOL file_name

If you do not follow the filename with a period and an extension, SPOOL adds a default file extension to the filename to identify it as an output file. The default varies with the host operating system; on most hosts it is LST or LIS. See the Oracle installation and user's manual(s) provided for your operating system for more information.

SQL*Plus continues to spool information to the file until you turn spooling off, using the following form of SPOOL:

SPOOL OFF

Creating a Flat File

When moving data between different software products, it is sometimes necessary to use a "flat" file (an operating system file with no escape characters, headings, or extra characters embedded). For example, if you do not have Net8, you need to create a flat file for use with SQL*Loader when moving data from Oracle7 to Oracle8.

To create a flat file with SQL*Plus, you first must enter the following SET commands:

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF

After entering these commands, you use the SPOOL command as shown in the previous section to create the flat file.

The SET COLSEP command may be useful to delineate the columns. For more information, see the SET command in Chapter 8.

Sending Results to a Printer

To print query results, spool them to a file as described in the previous section. Then, instead of using SPOOL OFF, enter the command in the following form:

SPOOL OUT

SQL*Plus stops spooling and copies the contents of the spooled file to your host computer's standard (default) printer. SPOOL OUT does not delete the spool file after printing.

Example 4-25 Sending Query Results to a Printer

To generate a final report and spool and print the results, create a command file named EMPRPT containing the following commands.

First, use EDIT to create the command file with your host operating system text editor. (Do not use INPUT and SAVE, or SQL*Plus will add a slash to the end of the file and will run the command file twice--once as a result of the semicolon and once due to the slash.)

SQL> EDIT EMPRPT

Next, enter the following commands into the file, using your text editor:

SPOOL TEMP
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN DEPTNO HEADING DEPARTMENT
COLUMN ENAME HEADING EMPLOYEE
COLUMN SAL HEADING SALARY FORMAT $99,999

BREAK ON DEPTNO SKIP 1 ON REPORT
COMPUTE SUM OF SAL ON DEPTNO
COMPUTE SUM OF SAL ON REPORT

SET PAGESIZE 21
SET NEWPAGE 0
SET LINESIZE 30

TTITLE CENTER 'A C M E  W I D G E T' SKIP 2 -
LEFT 'EMPLOYEE REPORT' RIGHT 'PAGE:' -
FORMAT 999 SQL.PNO SKIP 2

BTITLE CENTER 'COMPANY CONFIDENTIAL'

SELECT DEPTNO, ENAME, SAL
FROM EMP
ORDER BY DEPTNO;

SPOOL OUT

If you do not want to see the output on your screen, you can also add SET TERMOUT OFF to the beginning of the file and SET TERMOUT ON to the end of the file. Save the file (you automatically return to SQL*Plus). Now, run the command file EMPRPT:

SQL> @EMPRPT

SQL*Plus displays the output on your screen (unless you set TERMOUT to OFF), spools it to the file TEMP, and sends the contents of TEMP to your default printer:

         A C M E  W I D G E T

EMPLOYEE REPORT      PAGE:   1

DEPARTMENT EMPLOYEE     SALARY
---------- ---------- --------
        10 CLARK        $2,450
           KING         $5,000
           MILLER       $1,300
**********            --------
sum                     $8,750

        20 SMITH          $800
           ADAMS        $1,100
           FORD         $3,000
           SCOTT        $3,000
           JONES        $2,975

**********            --------
sum                    $10,875

         COMPANY CONFIDENTIAL

         A C M E  W I D G E T


EMPLOYEE REPORT      PAGE:   2

DEPARTMENT EMPLOYEE     SALARY
---------- ---------- --------
        30 ALLEN        $1,600
           BLAKE        $2,850
           MARTIN       $1,250
           JAMES          $900
           TURNER       $1,500
           WARD         $1,250
**********            --------
sum                     $9,400
**********            --------
sum                    $29,025

         COMPANY CONFIDENTIAL