SQL*Plus User's Guide and Reference Release 9.0.1 Part Number A88827-02 |
|
Formatting Query Results, 5 of 6
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
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 Oracle Net, you need to create a flat file for use with SQL*Loader when moving data from Oracle8 to Oracle9i.
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 SET MARKUP HTML OFF SPOOL 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.
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
SQL*Plus stores all information displayed on the screen after you enter the SPOOL command in the file you specify.
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.
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.)
EDIT EMPRPT
Next, enter the following commands into the file, using your text editor:
SPOOL TEMP CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN DEPARTMENT_ID HEADING DEPARTMENT COLUMN LAST_NAME HEADING 'LAST NAME' COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999 BREAK ON DEPARTMENT_ID SKIP 1 ON REPORT COMPUTE SUM OF SALARY ON DEPARTMENT_ID COMPUTE SUM OF SALARY ON REPORT SET PAGESIZE 24 SET NEWPAGE 0 SET LINESIZE 70 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 DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID; SPOOL OFF
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 and close the file in your text editor (you will automatically return to SQL*Plus). Now, run the command file EMPRPT:
@EMPRPT
SQL*Plus displays the output on your screen (unless you set TERMOUT to OFF), and spools it to the file TEMP:
A C M E W I D G E T EMPLOYEE REPORT PAGE: 1 DEPARTMENT LAST NAME MONTHLY SALARY ---------- ------------------------- -------------- 20 Hartstein $13,000 ********** -------------- sum $13,000 80 Russell $14,000 Partners $13,500 ********** -------------- sum $27,500 90 King $24,000 Kochhar $17,000 De Haan $17,000 ********** -------------- sum $58,000 -------------- sum $98,500 COMPANY CONFIDENTIAL 6 rows selected.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|