Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
EAN: 2147483647
Year: 1997
Pages: 391
Pages: 391
Authors: Advanced Information Systems
Page 305
| NOTE |
The remaining examples in this chapter include only the specific SQL*Plus commands necessary to produce the desired output. |
The following code shows the output report from Listing 13.3:
Thur Aug 31 page 1 Sales Detail by Sales Rep Sales Person: Elizabeth ---------------------------- Chrysler ,000 Chrysler ,500 Nissan ,000 Nissan ,000 Nissan ,500 Toyota ,500 ---------- Total: ,500 Sales Person: Emily ---------------------------- Ford ,000 Ford ,000 GM ,000 GM ,400 Nissan ,000 Nissan ,000 Toyota ,000 Toyota ,500 Toyota ,500 ---------- Total: ,400 Sales Person: Thomas ---------------------------- Chrysler ,500 Ford ,000 Ford ,000 GM ,400 GM ,600 GM ,000 Nissan ,000 Toyota ,000 ---------- Total: ,400
Page 306
Listing 13.3 creates a master/detail SQL*Plus report by using the SQL UNION command. In this example, there are six distinct, separate types of lines to be printed: the sales person (line 4), a line of dashes under the sales person (line 7), the detail line (line 10), a line of dashes under the detail total (line 14), a total line (line 17), and a blank line (line 21). There are six separate queries that have their output merged and sorted together by the SQL JOIN statement (lines 6, 9, 13, 16, 19, and 23). When you use JOIN to merge the output of two or more queries, the output result set must have the same number of columns . The headings are turned off (line 2) because regular SQL*Plus column headings are not desired for this type of report. The first column of each query has an alias column name of DUMMY. This DUMMY column is used to sort the order of the six types of lines (denoted by each of the six queries). The DUMMY column's only role is to maintain the order of the lines within the major sort field (SALES_REP_NO, in this example); therefore, the NOPRINT option is specified in line 3.
Listing 13.4 uses the JOIN feature to display output from two or more tables in the same report.
Listing 13.4. Multitable SQL*Plus report code.
1: column OBJECT_TYPE format a20 heading `Object' 2: column OBJECT_NAME format a8 heading `Name' 3: column COMMENT format a8 heading `Comments' 4: break on OBJECT_TYPE skip 1 5: ttitle `System Summary Report' 6: select `Program' OBJECT_TYPE, program_name OBJECT_NAME, 7: program_comments COMMENTS 8: from program_table 9: UNION 10: select `Command Language',cl_name, assoc_system 11: from cl_table 12: UNION 13: select `Files',file_name, `File Size = ` file_size `Bytes' 14: from file_table 15: /
Page 307
The following code shows the output report from Listing 13.4:
Thr Aug 31 page 1 System Summary Report Object Name Comments ------------------------ ---------- ------------------------ Programs AM1 Algebra Test 1 AM2 Algebra Test 2 AM3 Algebra Test 3 Command Language CL1 AM1 CL2 AM2 CL3 AM3 Files AM1.TST File Size = 1200 Bytes AM2.TST File Size = 3000 Bytes AM3.TST File Size = 2200 Bytes
Listing 13.4 creates a SQL*Plus report using different columns from different tables using the SQL UNION command. In this example, there are three different tables (lines 8, 11, and 14), but there are only three columns of output. The first query contains the column names (lines 6 and 7). This is because of the way the UNION operator works. The queries after the first query must follow the number of columns and the type of column (text or numeric) based on the column definitions of the first query. The BREAK command (line 4) causes the OBJECT_NAME to print once and creates the blank line between the groupings of records.
I will demonstrate two methods of creating reports that print with specific text in specific positions . Method 1 in Listing 13.5 uses the RPAD SQL function, whereas method 2 in Listing 13.6 uses the COLUMN formatting command. Both examples create the same output report.
Listing 13.5. Method 1: Fixed-position formatting SQL*Plus report code with RPAD.
1: define TICKET_ROWID = &1 2: set LINESIZE 80 3: set HEADING OFF 4: set FEEDBACK OFF 5: spool TICKET.OUT 6: select RPAD(`----------------------------------------------------' 7: null,80), 8: RPAD(` Customer Contact Survey' null,80), 9: RPAD(`------------------------------------------------' null,80), 10: RPAD(` Customer Name: ` CUSTOMER_NAME ` PHONE#: ` PHONE null,80), 11: RPAD(` Customer Address: ` CUSTOMER_ADDRESS null,80), 12: RPAD(` ` CUSTOMER_CITY CUSTOMER_STATE 13: CUSTOMER_ZIP null,80), 14: RPAD(`------------------------------------------------' null,80), 15: RPAD(` ` TO_CHAR(CONTACT_DATE,'mm/dd/yy HH:MI') ` Caller: ` CALLER 16: null,80), 17: RPAD(`------------------------------------------------' null,80), 18: RPAD(` Home Phone? ` HPHONE_YN `Best Time to call: ` CALL_TIME 19: null,80), 20: RPAD(` Has Catalog? ` CATALOG_YN `Desire Future Calls? ` FUTURE_YN 21: null,80),
Page 308
Listing 13.5. continued
22: RPAD(`------------------------------------------------' null,80), 23: RPAD(`PRINTED: ` TO_CHAR(SYSDATE,'mm/dd/yy HH:MI `BY: ` 24: OPERATOR null,80) 25: from CUSTOMER_TABLE 26: where ROWID = `&&TICKET_ROWID' 27: / 28: set PAGESIZE 1 29: set NEWPAGE 0 30: select null from dual; 31: set PAGESIZE 0 32: spool OUT 33: exit
Listing 13.6. Method 2: Fixed-position formatting SQL*Plus report code with COLUMN.
1: define TICKET_ROWID = &1 2: set PAGESIZE 55 3: set LINESIZE 80 4: set HEADING OFF 5: set FEEDBACK OFF 6: column LINE1 JUSTIFY LEFT NEWLINE 7: column LINE2 JUSTIFY LEFT NEWLINE 8: column LINE3 JUSTIFY LEFT NEWLINE 9: column LINE4 JUSTIFY LEFT NEWLINE 10: column LINE5 JUSTIFY LEFT NEWLINE 11: column LINE6 JUSTIFY LEFT NEWLINE 12: column LINE7 JUSTIFY LEFT NEWLINE 13: column LINE8 JUSTIFY LEFT NEWLINE 14: column LINE9 JUSTIFY LEFT NEWLINE 15: column LINE10 JUSTIFY LEFT NEWLINE 16: column LINE11 JUSTIFY LEFT NEWLINE 17: column LINE12 JUSTIFY LEFT NEWLINE 18: column LINE13 JUSTIFY LEFT NEWLINE 19: column LINE14 JUSTIFY LEFT NEWLINE 20: break ON ROW SKIP PAGE 21: SPOOL TICKET
Page 309
22: select `--------------------------------------------' null LINE1, 23: ` Customer Contact Survey' null LINE2, 24: `--------------------------------------------------' null LINE3, 25: ` Customer Name: ` CUSTOMER_NAME ` PHONE#: ` PHONE null LINE4, 26: ` Customer Address: ` CUSTOMER_ADDRESS null LINE5, 27: ` ` CUSTOMER_CITY CUSTOMER_STATE 28: CUSTOMER_ZIP null LINE6, 29: `--------------------------------------------------' null LINE7, 30: ` ` TO_CHAR(CONTACT_DATE,'mm/dd/yy HH:MI ` Caller: ` CALLER null 31: LINE8, 32: `--------------------------------------------------' null LINE9, 33: ` Home Phone? ` HPHONE_YN `Best Time to call: ` CALL_TIME null 34: LINE10, 35: ` `Has Catalog? ` CATALOG_YN `Desire Future Calls? ` FUTURE_YN null 36: LINE11, 37: `--------------------------------------------------' null LINE12, 38: `PRINTED: ` TO_CHAR(SYSDATE,'mm/dd/yy HH:MI `BY: ` OPERATOR null 39: LINE13, 40: `--------------------------------------------------' null LINE14 41: from CUSTOMER_TABLE 42: where ROWID = `&&TICKET_ROWID' 43: / 44: spool OUT 45: exit
Listings 13.5 and 13.6 both produce the same output report, which is shown in Listing 13.7.
Listing 13.7. Output of Listings 13.5 and 13.6: Fixed-position formatting SQL*Plus report.
--------------------------------------------------------------------------- Customer Contact Survey --------------------------------------------------------------------------- Customer Name: John Smith PHONE#: 800 555-1916 Customer Address: 123 Oak Street Anytown VA 12345 --------------------------------------------------------------------------- 31-Aug-95 10:05 Caller: DHotka --------------------------------------------------------------------------- Home Phone? Y Best Time to call: 8pm Has Catalog? Y Desire Future Calls? N --------------------------------------------------------------------------- PRINTED: 31-Aug-95 12:45 BY: KLeigh
---------------------------------------------------------------------------
Listings 13.5 (method 1) and 13.6 (method 2) produce the exact same output, as shown in Listing 13.7. Both these methods produce reports with information in fixed or predefined positions. Both these methods could be used to print information on a preprinted form. These particular examples were designed to be started from inside another process, such as SQL*Forms, because the only input parameter is an Oracle row ID used to read and process a single row from the database (see lines 1 and 26 in Listing 13.5 and lines 1 and 42 in Listing 13.6).
These examples use the concatenation feature of SQL () to blend text between database fields. Each column in the SQL statement represents an individual line in the report. Both examples have the standard column headings feature turned off (line 3 of Listing 13.5 and line 4 of Listing 13.6). Both examples have a one-to-one relationship between a SQL column and a line of output. The methods differ in how the columns are formatted to create the individual lines.
The main difference in these two methods is the approach used in the individual line setup. Method 1 (Listing 13.5) uses the SQL command RPAD (line 6) with LINESIZE (line 2) to create an output line. The RPAD is used to fill the line with blanks to position 80 and, with LINESIZE set at 80, causes the formatted line to appear on a line by itself. Method 2 (Listing 13.6) uses the column command with the option NEWLINE specified with a field alias name (lines 6 and 22). The column command with the NEWLINE option makes the formatted line appear on a line by itself.