Lesson 11	Page headers and footers
Objective	Add page headers and footers to a report in Oracle

SQL*Plus Report Page Headers and Footers (TTITLE, BTITLE)

When you generate a text-based report from Oracle using SQL*Plus (or SQLcl in SQL*Plus-compatibility mode), you can add a page header and page footer. In SQL*Plus terminology, these are called titles: TTITLE prints at the top of each page, and BTITLE prints at the bottom.

Oracle 23ai note: TTITLE/BTITLE are still supported for legacy scripts, but they are client-side formatting commands. For modern automation and tooling, Oracle workflows increasingly favor SQLcl structured output (HTML/CSV/JSON) or application-level reporting (SQL Developer, APEX, BI Publisher). This lesson teaches TTITLE/BTITLE because you will encounter them in existing scripts and course material.

TTITLE syntax example

TTITLE LEFT  "COIN Database"
       RIGHT "Page" FORMAT 999 SQL.PNO
       SKIP 1 CENTER "Database Object Report" -
       SKIP 6

TTITLE defines a page header (top title). SQL*Plus also accepts the abbreviation TTI.
LEFT aligns the next text item with the left edge of the current line width.
"COIN Database" prints on the first title line at the left.
RIGHT aligns the next text item with the right edge of the current line width.
"Page" prints before the page number.
FORMAT 999 formats the numeric value that follows (here, the page number).
SQL.PNO is a SQL*Plus substitution that prints the current page number.
SKIP 1 advances one line in the title area.
CENTER centers the next text item on the new title line.
"Database Object Report" prints centered on the second title line.
The trailing hyphen (-) continues the TTITLE command onto the next line.
SKIP 6 leaves vertical space between the title and the column headings/data.

Think of TTITLE and BTITLE as a left-to-right instruction list. Each time SQL*Plus advances to a new page, it replays those instructions to draw the header and footer.

Important: Do not place free text immediately after TTITLE or BTITLE. Always begin with a positioning keyword such as LEFT, CENTER, or RIGHT. If you omit the keyword, SQL*Plus may interpret the command using older compatibility rules and you can get unexpected output.

BTITLE (page footer) works the same way

BTITLE CENTER "Confidential" SKIP 1

Centering, right-alignment, and LINESIZE

The center and right edge of a SQL*Plus “page” are based on the current line width, not your printer paper. By default, SQL*Plus uses 80 characters per line. That makes character 80 the right edge and character 40 the center.

If your report output is narrower (or wider), set LINESIZE so the right-aligned and centered title text lines up with your actual report width.

SET LINESIZE 54

With a narrow report (about 54 characters wide), setting LINESIZE 54 makes the “right” edge of the title match the right edge of the report output.

COIN Database                                  Page  1
                 Database Object Report

PAGESIZE

Just as LINESIZE controls characters per line, PAGESIZE controls lines per page. SQL*Plus defaults to 24. You can increase it for longer pages or decrease it to force more frequent headers.

SET PAGESIZE 50

Why this matters for footers: before printing a BTITLE, SQL*Plus will output enough blank lines to push the footer to the bottom of the current page. If your PAGESIZE is too small (or your query output is taller than expected), the footer placement may look odd. Choose a PAGESIZE that matches how you intend the report to paginate.

Modern Oracle 23ai direction (recommended for new work)

For new reporting scripts, prefer SQLcl structured output rather than TTITLE/BTITLE formatting. This makes reports easier to archive, parse, and feed into tooling (including AI review pipelines).

-- SQLcl examples (client-side):
-- SET MARKUP HTML ON
-- SET SQLFORMAT CSV
-- SET SQLFORMAT JSON

Oracle Report Page Headers and Footers - Exercise

Take this exercise to try out the TTITLE and BTITLE commands.
Oracle Report Page Headers Footers - Exercise