0301-0304
Page 301
| NOTE |
If you issue SPOOL filename without issuing a SPOOL OFF or SPOOL OUT, the current operating system_dependent file is closed and the new one, as specified by the SPOOL command, is opened. |
| TIP |
I prefer to write SQL*Plus-based reports by using the SET variables mentioned previously with the SPOOL command. I create the output report in the file specified by the SPOOL command and then control whether it is displayed to the terminal, optionally printed, or both by using operating system_dependent command language. |
| TIP |
I always use a file suffix when specifying SPOOL filename. This enables me to control exactly what the entire filename is instead of depending on Oracle default options (the LST suffix feature), which are subject to change without notice with newer releases of the SQL*Plus product. |
- UNDEFINE variable
UNDEFINE removes the previously DEFINEd variable from the SQL*Plus environment.
Access Commands for Various Databases
The database-access commands CONNECT, DISCONNECT, and COPY are used to connect to and share data with other Oracle databases. (A discussion of these commands is beyond the scope of this chapter.)
SQL*Plus Reporting
You can use SQL*Plus formatting commands in a variety of combinations to create reports. This section covers reporting techniques that use control breaks, different ways to format headings, input parameters, SQL*Plus environment controls, and the SQL UNION operator.
Reporting Techniques
This section covers some common SQL*Plus report-formatting features. It also covers techniques for controlling the resulting output. You'll see examples of both simple and advanced reporting techniques in this section.
Page 302
Listing 13.1 formats the results of a SQL query. It defines a report title and formats, assigns column headings, and applies some control breaks for intermediate and report totaling .
Listing 13.1. Simple SQL*Plus report code.
1: define ASSIGNED_ANALYST = &1 2: set FEEDBACK OFF 3: set VERIFY OFF 4: set TERMOUT OFF 5: set ECHO OFF 6: column APPLICATION_NAME format a12 heading `Application' 7: column PROGRAM_NAME format a12 heading `Program' 8: column PROGRAM_SIZE format 999999 heading `ProgramSize' 9: break on APPLICATION_NAME skip 2 10: break on report skip 2 11: compute sum of PROGRAM_SIZE on APPLICATION_NAME 12: compute sum of PROGRAM_SIZE on report 13: ttitle `Programs by Application Assigned to: &&ASSIGNED_ANALYST' 14: spool ANALYST.OUT 15: select APPLICATION_NAME,PROGRAM_NAME,nvl(PROGRAM_SIZE,0) 16: from APPLICATION_PROGRAMS 17: where ASSIGNED_NAME = `&&ASSIGNED_ANALYST' 18: order by APPLICATION_NAME,PROGRAM_NAME 19: / 20: spool off 21: exit
The following is the output report from the code in Listing 13.1:
Tue Jul 13 page 1 Programs by Application Assigned to: BILLK Program Application Program Size ----------- ------------ --------- COBOL CLAIMS 10156 HOMEOWN 22124 PREMIUMS 10345 --------- sum 42625 FORTRAN ALGEBRA 6892 MATH1 7210 SCIENCE1 10240 --------- sum 24342 sum 66967
Listing 13.1 is a simple but common form of SQL*Plus formatting. This report passes a command-line parameter (&1 on line 1) and assigns it to the variable name ASSIGNED_ANALYST. The ASSIGNED_ANALYST variable is then used in the headings (line 13) and again as part of the SQL query (line 17). Lines 2 through 5 suspend all terminal output from the SQL*Plus environment. The && is used to denote substitution of an already defined variable. This report
Page 303
contains two breaks: one when the column APPLICATION_NAME changes (line 9) and one at the end of the report (line 10). Totals also are calculated for each of these breaks (lines 11 and 12). The pipe character () in the TTITLE command (line 13) moves the following text onto its own line. Line 13 opens an operating-system_dependent file named ANALYST.OUT in the current operating-system_dependent directory. The order by clause of the query on line 18 ensures that the breaks occur in an orderly manner.
| TIP |
Always order the query output by the breaks expected by the program. The only way to guarantee the order of the rows is to use an order by clause on the query. |
Advanced Reporting Techniques
Listing 13.2 creates a cross-tabular report with a spreadsheet appearance.
Listing 13.2. Cross-tabular SQL*Plus report code.
1: define RPT_DATE = &1 2: set FEEDBACK OFF 3: set VERIFY OFF 4: set TERMOUT OFF 5: set ECHO OFF 6: column SALES_REP format a12 heading `SalesPerson' 7: column NISSAN format 999999 heading `Nissan' 8: column TOYOTA format 999999 heading `Toyota' 9: column GM format 999999 heading `GM' 10: column FORD format 999999 heading `Ford' 11: column CHRYSLER format 999999 heading `Chrysler' 12: column TOTALS format 999999 heading `Totals' 13: break on report skip 2 14: compute sum of NISSAN on report 15: compute sum of TOYOTA on report 16: compute sum of GM on report 17: compute sum of FORD on report 18: compute sum of CHRYSLER on report 19: compute sum of TOTALS on report 20: ttitle left `&&IN_DATE' center `Auto Sales' RIGHT `Page: ` format 999 - 21: SQL.PNO skip CENTER ` by Sales Person ` 22: spool SALES.OUT 23: select SALES_REP, 24: sum(decode(CAR_TYPE,'N',TOTAL_SALES,0)) NISSAN, 25: sum(decode(CAR_TYPE,'T',TOTAL_SALES,0)) TOYOTA, 26: sum(decode(CAR_TYPE,'G',TOTAL_SALES,0)) GM, 27: sum(decode(CAR_TYPE,'F',TOTAL_SALES,0)) FORD, 28: sum(decode(CAR_TYPE,'C',TOTAL_SALES,0)) CHRYSLER , 29: sum(TOTAL_SALES) TOTALS 30: from CAR_SALES 31: where SALES_DATE <= to_date(`&&RPT_DATE') 32: group by SALES_REP
Page 304
Listing 13.2. continued
33: / 34: spool off 35: exit
The following code shows the output report from Listing 13.2:
31-AUG-95 Auto Sales Page: 1 by Sales Person Sales Person Nissan Toyota GM Ford Chrysler Totals -------- -------- --------- -------- -------- -------- ------ Elizabeth 5500 2500 0 0 4500 12500 Emily 4000 6000 4400 2000 0 16400 Thomas 2000 1000 6000 4000 1500 14500 -------- --------- -------- -------- -------- ------ 11500 9500 10400 6000 6000 43400
Listing 13.2 is a cross-tabular SQL*Plus command file. This report passes a command-line parameter (&1 on line 1) and assigns it to the variable name RPT_DATE. The RPT_DATE variable is then used in the headings (line 20) and again as part of the SQL query (line 31). Lines 2 through 5 suspend all terminal output from the SQL*Plus environment. The report is created in the operating system_dependent file SALES.OUT. Column-formatting commands control the appearance of the columns (lines 6 through 12). The combination of compute commands (lines 14 through 19), the sum statements in the query (lines 24 through 29), and the group by clause in the query (line 32) give the report output the appearance of a cross-tabular report.
| NOTE |
I used a TTITLE technique in Listing 13.2 (lines 20 and 21) different from that of Listing 13.1 (line 13). |
Listing 13.3 displays a major break field with the supporting data immediately following.
Listing 13.3. Master/detail SQL*Plus report code.
1: ttitle `Sales Detail by Sales Rep' 2: set HEADINGS OFF 3: column DUMMY NOPRINT 4: select 1 DUMMY, SALES_REP_NO,'Sales Person: ` SALES_REP 5: from sales 6: UNION 7: select 2 DUMMY,SALES_REP_NO,'--------------------' 8: from sales 9: UNION 10: select 3 DUMMY,SALES_REP_NO, rpad(CAR_MAKE,4) ` ` 11: to_char(SALE_AMT,'9,999.99') 12: from sales_detail 13: UNION 14: select 4 DUMMY,SALES_REP_NO,' ----------' 15: from sales 16: UNION 17: select 5 DUMMY,SALES_REP_NO,'Total: ` 18: to_char(sum(TOTAL_SALES),'9,999.99') 19: from sales 20: UNION 21: select 6 DUMMY,SALES_REP_NO,' ` 22: from sales 23: order by 2,1,3 24: /
EAN: 2147483647
Pages: 391