|SQL*Plus User's Guide and Reference
Formatting Query Results, 5 of 5
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:
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:
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.
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:
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.)
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*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