Building Reports Using Oracle SQL*Plus

In Chapter 2, “Fundamentals of the SQL Language,” you learned how to use SQL*Plus to submit SQL statements to Oracle9i for execution and a result set. We have also discussed how to manipulate SQL statements in the SQL*Plus SQL buffer. Oracle9i also has a SQL buffer where all SQL statements are submitted for execution. The Oracle9i SQL buffer accepts only ANSI standard SQL statements, or just SQL—not any of the SQL*Plus buffer commands or formatting commands. Figure 6.13 shows how SQL*Plus and Oracle9i relate together. SQL*Plus (and any other program working with Oracle9i) submits its SQL statements to Oracle9i for processing via Net8. Oracle9i puts this SQL statement in the SQL buffer for execution, where it is parsed (syntax checking), prepared (a plan on how Oracle9i will get the data, possibly using indexes), and executed. Then rows are returned back through Net8 to SQL*Plus. SQL*Plus then applies any of its formatting commands to the result set and displays the results on the end user's terminal or writes them to an operating system file (depending on the options given in the SQL*Plus session).

This section teaches you how to use SQL and SQL*Plus commands to format the output from the queries. The SQL*Plus SQL buffer holds only one command at a time.

The SQL*Plus COLUMN command (illustrated with SQL Help in Figure 6.14) is useful for giving columns a meaningful format, applying dollar signs ($) and commas to numeric fields, and providing a better column title. Figure 6.15 shows how to apply this technique to a SQL query. The COLUMN FORMAT command correctly sizes the fields and applies the numeric mask. Also notice the heading fields and the fact that the ENAME column contains a vertical bar (|). Whenever any special character or space appears in the title, it must be enclosed with single quotes. The single vertical bar tells SQL*Plus to put each line of the heading on a separate line.

The SQL language offers an alias, which is the ability to give columns more complete names or give a calculated field a name. Figure 6.16 shows how an alias is used in a SQL query. Aliases have many uses. Here they provide a better heading to the SQL statement. Aliases are also useful for giving calculated columns better headings and true names. Figure 6.16 also shows how you can use an alias to give a column a name and then use the SQL*Plus COLUMN command to give some further definition to the output. Notice that the COLUMN FORMAT command and the alias name are the same.

SQL statement output also can be ordered together by using the BREAK command (see Figure 6.17). This SQL*Plus command, used in conjunction with the ORDER BY clause, suppresses values on the break column, giving a master/detail appearance, as seen in Figure 6.18. Notice that BREAK and ORDER BY contain the same columns. BREAK also can handle breaks on more than one column, but ORDER BY will need the same columns and in the same order to provide the correct results.

The SQL*Plus COMPUTE command is useful for creating subtotals and grand totals with the BREAK command. Figure 6.19 shows the syntax for the COMPUTE command, and Figure 6.20 shows the COMPUTE command in use with the BREAK command. The example has two separate COMPUTE commands: one for the DEPTNO breaks and one for the break on report, which causes the final total to appear.

The COMPUTE command can do more than simply add fields (which is accomplished with the SUM option). Other COMPUTE options include the following:

AVG	Average value of non-null values
COUNT	Number of non-null values
MAX	Highest value
MIN	Lowest value
NUMBER	Number of rows
STD	Standard deviation
VAR	Variance

Chapter 7, “Using Advanced SQL Techniques and SQL*Plus Reporting Features,” contains additional ways of using SQL*Plus to format reports.

The final example in this chapter will use the UNION operator to create a master/detail type report. The ST_INVENTORY_DETAIL report will contain information from three different tables: ST_INVENTORY, ST_PARTS, and ST_BILL_TIME. The UNION operator will enable a report to easily contain the output from these three database tables.

Listing 6.1 creates a Master/Detail SQL*Plus report by using the SQL UNION command. In this example, nine distinct separate types of lines are to be printed: the Vehicle Type line (line 24), a line of dashes before the final total (line 60), the Purchase Price (line 28), the Sale Price (line 32), the Parts header (line 39), the Parts detail line (line 42), the Labor Used header line (line 49), the labor detail line (line 52), and the total line (63). In addition, a few blank lines will be included (lines 36, 46, and 57). Thirteen separate queries are used, which have their output merged and sorted together by the SQL JOIN statement (see lines 24, 28, 32, 36, 39, 42, 46, 49, 52, 57, 60, 63, and 66).

When using JOIN to merge the output of two or more queries, the output result set must have the same number of columns and column types. The headings are turned off (line 17) 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 13 queries). The DUMMY column's only role is to maintain the order of the output lines, so the NOPRINT option is specified in line 21. The final ORDER BY (line 68) actually merges the result set lines to form the report in Listing 6.1. Notice the use of the TO_CHAR function to ensure that the output from this query is indeed character mode for the UNION operator. Also notice that each of the queries returns two columns: DUMMY and a character string. Each SQL query builds one output line. The SQL queries on ST_PARTS and ST_BILL_TIME might return zero or more rows. The DUMMY column will maintain the order of the output lines.

Figure 6.21 shows the output report from Listing 6.1.

Listing 6.1. ST_INVENORY_DETAIL.SQL

1: rem 2: rem ST_Inventory_Detail.SQL - Demonstrates how to create a Master/Detail 3: rem report using the UNION operator. This technique is 4: rem useful whenever records/text from different tables 5: rem need to appear in the same report. 6: rem 7: rem Oracle9i From Scratch 8: rem by Dan Hotka 9: rem Que Publications March 2000 10: rem All Rights Reserved 11: rem 12: ACCEPT INV_ID PROMPT 'Enter Inventory ID --> ' 13: SET FEEDBACK OFF 14: SET VERIFY OFF 15: SET LINESIZE 60 16: SET PAGESIZE 24 17: SET HEADING OFF 18: 19: TTITLE 'Inventory Detail for Inventory_ID &INV_ID' 20: 21: COLUMN DUMMY NOPRINT 22: 23: SPOOL ST_Inventory_Detail.OUT 24: SELECT 1 DUMMY, 'Vehicle: ' || inv_year || ' ' || inv_color || ' ' || inv_make 25: FROM st_inventory 26: WHERE inv_id = &INV_ID 27: UNION 28: SELECT 2 DUMMY, 'Purchase Price: ' || TO_CHAR(inv_purchase_amt,'$999,999') 29: FROM st_inventory 30: WHERE inv_id = &INV_ID 31: UNION 32: SELECT 3 DUMMY, 'Sale Price: ' || TO_CHAR(NVL(inv_sale_amt,0),'$999,999') 33: FROM st_inventory 34: WHERE inv_id = &INV_ID 35: UNION 36: SELECT 4 DUMMY, ' ' 37: FROM dual 38: UNION 39: SELECT 5 DUMMY, 'Parts Used: ' 40: FROM dual 41: UNION 42: SELECT 6 DUMMY, RPAD(part_desc,20) || ' ' || TO_CHAR(part_amt,'$999,999') 43: FROM st_parts 44: WHERE part_inv_id = &INV_ID 45: UNION 46: SELECT 7 DUMMY, ' ' 47: FROM dual 48: UNION 49: SELECT 8 DUMMY, 'Labor Used: ' 50: FROM dual 51: UNION 52: SELECT 9 DUMMY , RPAD(staff_name,10) || ' ' || TO_CHAR(bt_time *staff_billing_rate,'$999,999') 53: FROM st_bill_time, st_staff 54: WHERE st_bill_time.bt_inv_id = &INV_ID 55: AND st_bill_time.bt_staff_id = st_staff.staff_id 56: UNION 57: SELECT 10 DUMMY, ' ' 58: FROM dual 59: UNION 60: SELECT 11 DUMMY, ' ------------' 61: FROM dual 62: UNION 63: SELECT 12 DUMMY, 'Profit/Loss ' || TO_CHAR(st_calc_profit(&INV_ID),'$999,999') 64: FROM dual 65: UNION 66: SELECT 13 DUMMY, ' ' 67: FROM dual 68: ORDER BY 1,2 69: / 70: SPOOL OFF 71: 72: ACCEPT anything PROMPT 'Hit Enter when done viewing report' 73: EXIT