2.7 How do I…Use SQL*Plus as a report writer?Problem
I am often called on to develop quick reports for top management. The query portion of the report is the most complicated part. I need to format the output of the query so it can be printed as a report. I know SQL*Plus has many report formatting functions. What are they and how do I use them?
Technique
SQL*Plus can be used as a reporting tool. SQL*Plus contains a complete set of formatting commands. System variables can be set to control the behavior of SQL*Plus while the report runs. In this section, you will go through the steps required to format a report. Because a report generated in SQL*Plus contains many SQL*Plus statements, it will be much easier to develop the report using Windows Notepad. Although SQL*Plus can be used to edit a SQL statement, I don’t recommend it for complicated scripts.
Steps
1. Run SQL*Plus and connect as the WAITE user account. Execute CHP2_7.SQL, using the START command to create the sample tables used in this How-To.
SQL> START CHP2_7.sql
SQL>
2. Launch Windows Notepad and create the SQL statement that is the basis for the report. The SQL statement shown in Figure 2.12 is the basis for the report presented in this How-To.
Save the file to the working directory of SQL*Plus. Use the START command to run the SQL command file. Figure 2.13 displays the unformatted output of CHP2_11.SQL in SQL*Plus.
Because no formatting commands have been executed, the output is unacceptable as a report. The next step formats the columns of the report.
3. Change the column headings to descriptions that are more meaningful to the user. Column headings are formatted in SQL*Plus using the COLUMN command. Insert these lines at the beginning of the file:
COLUMN DEPTNO FORMAT 9999 HEADING “Dept”
COLUMN DNAME FORMAT A15 HEADING “Department | Name”
COLUMN EMPNO FORMAT 9999 HEADING “ “
COLUMN ENAME FORMAT A15 HEADING “Employee | Name” TRUNCATE
COLUMN SALARY FORMAT $999,999 HEADING “Salary”
CHP2_12.SQL shows the script as it should look after this step. Carefully note the different formats a column can have. DEPTNO and EMPNO are formatted as four-digit numbers; SALARY is formatted as money containing a dollar sign and commas. DNAME and ENAME are formatted as 15-character text strings. The TRUNCATE clause at the end of the COLUMN command for the ENAME column truncates the output if it is longer than 15 characters; otherwise, the output would be wrapped within a 15-character column. The Employee | Name heading contains the heading separator character. This causes the heading to be printed as:
Employee
Name
You can view the current value of the heading separator character with the SHOW HEADSEP command.
4. Save the file and run it with the START command. Figure 2.14 shows the output of CHP2_12.SQL with formatted columns.
The output is starting to look very much like a report. In the next step, a title is added to the beginning and end of each page, and the row count at the end of the query is removed.
5. Add the commands to provide a title for the report. CHP2_13.SQL contains the script as it will look after this step. The title can be printed at the bottom or top of each page by using the BTITLE and TTITLE commands, respectively. The following commands are added to the beginning of the file to create a top and bottom title:
TTITLE CENTER ‘Salary Report’ SKIP 1 LINE
BTITLE LEFT ‘Page: ‘ format 999 sql.pno
SET LINESIZE 80
SET FEEDBACK OFF
The TTITLE command creates a title for the top of each page. The SKIP 1 LINE clause makes the report skip one line after printing the title. The CENTER clause centers the title, based on the width of the line represented by the LINESIZE system variable. If the title requires multiple lines, they are appended to the TTITLE command. For example:
TTITLE CENTER ‘Salary Report’ SKIP 1 LINE LEFT ‘CHAP2-SQL’
centers the Salary Report line, skips one line, and left-justifies CHAP2-SQL on the next line. The BTITLE command creates a title for the bottom of each page. The sql.pno variable used in this statement always represents the current page number. The SET LINESIZE 80 command sets the width of the line to 80 characters. The SET FEEDBACK OFF command eliminates the row count from the end of the query.
6. Save the file and run the script to generate the report as it looks after Step 5. Figure 2.15 shows the output of CHP2_12.SQL after the titles are created for the report.
The output of the query is beginning to look like a report. In the next step, common data are grouped together to give the report a more pleasing appearance.
7. Add the lines required to group repeating values within SQL*Plus. In the query, data is ordered by DEPTNO and DNAME. In the output of the query, there is repeating DEPTNO and DNAME data.
The BREAK command groups together repeating occurrences of values. Adding the following statements to the file will group the output on the DEPTNO and DNAME fields. CHP2_13.SQL shows how the script will look after this step.
BREAK ON DEPTNO ON DNAME ON REPORT SKIP 1
The SKIP 1 clause leaves a blank line in the report after each grouping. You can vary the number of lines skipped or change the number to SKIP 1 PAGE to move to the top of a new page after each data group.
8. Save the file and run the report to show the results after the last step. Figure 2_16 shows the output of CHP2_13.SQL within SQL*Plus.
In the next step, the salaries are totaled for each of the departments and a grand total calculated for the report.
9. Add the lines required to provide totals and subtotals. The COMPUTE command enables calculations to be created for grouped data. The following statement calculates the sum of the SALARY column for each distinct occurrence of DEPTNO and generates a grand total for the report. The CHP2_14.SQL file contains the final results of this operation.
COMPUTE SUM OF SALARY ON DEPTNO REPORT
10. Save the file and run the report. Figure 2.17 shows the final output of this process.
In addition to calculating the sum of columns, the group calculations shown in Table 2.3 can be performed.
Table 2.3 Compute functions Compute Clause Results AVG Average. COUNT Count, not NULL columns only. MAX The largest value. MIN The smallest value. STD The standard deviation. SUM The sum. VAR The variance. NUM Count, including NULL columns. How It Works
A report created with SQL*Plus can be a large query formatted using SQL*Plus commands and controlled with system variables. Table 2.4 contains the com-mands used most often in formatting reports.
Step 1 creates the sample tables that are queried in the How-To. Step 2 presents a query that is formatted in the How-To. Steps 3 and 4 use the COLUMN command to format the columns retrieved by the query. Steps 5 and 6 use the TTITLE and BTITLE commands to create titles for the report. Steps 7 and 8 use the BREAK command to group common data together. Steps 9 and 10 use the COMPUTE command to create subtotals and totals for the report.
Table 2.4 Report formatting commands Command Definition BREAK ON Controls where spaces are placed between sections and where to break for subtotals and totals. BTITLE Sets the bottom title for each page of the report. COLUMN Sets the heading and format of a column. COMPUTE Makes SQL*Plus compute a variety of totals. REMARK Identifies the words that follow as comments. SAVE Saves the contents of the SQL buffer to a file. SET LINESIZE Sets the width of a line in characters for the report. SET NEWPAGE Sets the number of lines between pages of a report. SPOOL Tells SQL*Plus to write output to a file. START Tells SQL*Plus to execute a file. TTITLE Sets the title for each page of the report. Comments
SQL*Plus is a valid tool for creating reports using the Oracle database. In many cases, you will end up using SQL*Plus to prototype the SQL statement used in your report writer.