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:

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.

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.