SQL*Plus reports are columnar in nature. SQL*Plus provides you with the ability to define column headings and display formats for each column in a report. You may also define page headers and footers, page and line breaks, and summary calculations such as totals and subtotals.
Specify column headings using the HEADING clause of the COLUMN command:
COLUMN employee_name HEADING "Employee Name"
Either single or double quotes may be used to enclose the heading text. The resulting heading will look like this:
Employee Name -------------
To specify a multiline heading, use the vertical bar
(|
) character to specify the location of the line
break. For example:
COLUMN employee_name HEADING "Employee|Name"
The resulting multiline heading will look like this:
Employee Name ---------
Headings of text columns are aligned to the left. Headings of numeric columns are aligned to the right. Use the JUSTIFY clause to alter that behavior:
COLUMN employee_name HEADING "Employee|Name" - JUSTIFY RIGHT COLUMN employee_name HEADING "Employee|Name" - JUSTIFY CENTER
Use SET HEADSEP to change the line-break character to something other than a vertical bar. Use SET UNDERLINE to change the underline character to something other than a hyphen.
Specify display formats using the FORMAT clause of the COLUMN command. For numeric fields, format specifications can be quite detailed—controlling the length, the number of decimal places, and the punctuation used in the number. For text and date fields, you can control the column width and whether the column wraps. The Section 1.6 section, later in this book, shows you how to format different types of data.
Page width is controlled by the SET LINESIZE command. The default width is 80 characters. You can change it—to 60 characters, for example—by using the command like this:
SET LINESIZE 60
The LINESIZE setting is used by SQL*Plus to center and right-justify page headers and page footers.
Page length is controlled by the SET PAGESIZE command. The default is to print 24 lines per page, and this includes the page header and page footer lines. The following command changes the page length to 50 lines:
SET PAGESIZE 50
Setting PAGESIZE to zero has a special meaning to SQL*Plus. A PAGESIZE of zero will inhibit the display of page headers, page footers, and column headings.
Define page headers and footers using the TTITLE and BTITLE commands. TTITLE, for top title, defines the page header. BTITLE, for bottom title, defines the page footer. The syntax is identical for both.
The following example defines a multiline page header with the company name on the left and the page number on the right:
TTITLE LEFT "My Company" CENTER "Current" - RIGHT "Page" FORMAT 999 SQL.PNO SKIP 1 - CENTER "Employee Listing" SKIP 4
The resulting title will look like this:
My Company Current Page 1 Employee Listing
The final SKIP clause provides three blank lines between the page title and the column headers. The same clauses work in the BTITLE command to define page footers.
To get the current date into a page title, you must:
Get the date into a user variable.
Place the user variable into your BTITLE or TTITLE command.
You can use the following commands in a SQL*Plus script to get the current date into a user variable:
SET TERMOUT OFF COLUMN curdate NEW_VALUE report_date SELECT TO_CHAR(SYSDATE,'dd-Mon-yyyy') curdate FROM DUAL; SET TERMOUT ON
After executing the commands shown here, the date will be in a user variable named REPORT_DATE. The following command places that value into a page footer:
BTITLE LEFT "Report Date: " report_date
This same technique can also be used to retrieve other values from the database and place them in either a page header or page footer.
By default, SQL*Plus will print one blank line between each page of output. That blank line, added to the PAGESIZE setting, must equal the physical size of the pages in your printer.
The SET PAGESIZE command may be used to control the number of lines SQL*Plus prints on a page. SET NEWPAGE controls SQL*Plus’ action when a page break occurs. You can change the number of blank lines between pages by using a command such as this:
SET NEWPAGE 10
You can tell SQL*Plus to display one form-feed character between pages by setting NEWPAGE to zero. For example:
SET NEWPAGE 0
Newer releases of SQL*Plus also allow SET NEWPAGE NONE, which eliminates both blank lines and form-feed characters from between pages.
The BREAK and COMPUTE commands may be used to define breaks and summary calculations for a report. BREAK also allows you to inhibit the display of repetitive column values.
To eliminate repetitive column values, use the BREAK command as shown in this example:
SQL> BREAK ON owner SQL> SELECT owner, table_name 2 FROM all_tables 3 ORDER BY owner, table_name; OWNER TABLE_NAME ========== =============== CTXSYS DR$CLASS DR$DELETE DR$INDEX DEMO CUSTOMER DEPARTMENT EMPLOYEE
When you list a column in the BREAK command, SQL*Plus prints the value of the column only when it changes. It’s very important that you remember to sort the query results of the same column.
You can also use the BREAK command to skip lines or skip to a new page whenever a value changes. For example:
BREAK ON owner SKIP 1 BREAK ON owner SKIP PAGE
The first command results in printing a blank line whenever the owner changes. The second results in a page break each time the owner changes.
Multiple breaks may be specified for a report, but that’s always done using just one command. The following example causes a page break to occur whenever an owner changes and a blank line to be printed whenever the object type changes:
BREAK ON owner SKIP PAGE ON object_type SKIP 1 SELECT owner, object_type, object_name FROM dba_objects ORDER BY owner, object_type, object_name;
Before performing the break actions for a column, SQL*Plus will first perform the break actions for all inner columns. In this case, a change in the owner field would result in one skipped line and then a page break.
The COMPUTE command tells SQL*Plus to compute summary values for a group of records. COMPUTE is always used in tandem with BREAK. For example, to compute the number of tables owned by each user, you could do the following:
BREAK ON owner COMPUTE COUNT OF table_name ON owner SELECT owner, table_name FROM dba_tables ORDER BY owner, table_name;
SQL*Plus counts the number of table names for each distinct owner value and displays the results whenever a break occurs in the owner field.
You can compute summaries on multiple columns at once by using multiple COMPUTE commands. The following example counts the number of objects of each type and sums the extent sizes for each object:
COMPUTE SUM OF bytes - ON segment_name COMPUTE COUNT OF segment_name - ON segment_type BREAK ON segment_type ON segment_name SELECT segment_name, segment_type, bytes FROM user_extents ORDER BY segment_type, segment_name;
Notice that the display order—the order used in the SELECT list—does not need to match the sort order or the break order. Also notice that multiple summaries are defined using multiple COMPUTE commands, but multiple breaks are defined using just one BREAK command.