SQL*Plus Report Page Headers and Footers with TTITLE and BTITLE

When you generate a text-based report from Oracle using SQL*Plus, you can add a page header and a page footer. SQL*Plus calls these titles: TTITLE prints at the top of each page, and BTITLE prints at the bottom. This lesson covers both commands in depth, along with the two settings, LINESIZE and PAGESIZE, that determine exactly where titles land on the page.

TTITLE and BTITLE remain fully current in Oracle AI Database 26ai, but it is worth knowing where they sit relative to newer tooling. SQLcl offers structured output formats, HTML, CSV, and JSON among them, that are often easier to archive, parse, or feed into other tools than a plain text report is. This lesson focuses on TTITLE and BTITLE regardless, since they remain the standard way to format a readable, printable text report, and you will encounter them constantly in existing scripts and documentation.

Here is a representative TTITLE command, building a two-line header:

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

Working through each piece: TTITLE defines a page header, and SQL*Plus also accepts the abbreviation TTI. LEFT aligns the following text item to the left edge of the current line width, so "COIN Database" prints flush left. RIGHT aligns the next item to the right edge instead; here it prints the literal text "Page" followed by a formatted number. FORMAT 999 controls how that number displays, and SQL.PNO is the system-maintained variable holding the current page number, so together they print something like "Page 1" flush right. SKIP 1 advances one line within the title area, CENTER centers the next text item on that new line, and the trailing hyphen continues the command onto another line rather than ending it. SKIP 6 finally leaves six blank lines between the title and the actual column headings and data that follow.

Think of TTITLE and BTITLE as a left-to-right instruction list, one SQL*Plus replays in full every time it starts a new page. Entering TTITLE by itself, with no clauses at all, lists whatever definition is currently active, the same convention already covered for BREAK in the previous lesson.

One warning worth taking seriously: do not place free text immediately after TTITLE or BTITLE without a positioning keyword first. The exact trigger condition is precise, not just a general caution: SQL*Plus falls back to an older, compatibility form of TTITLE specifically when only a single word or a single quoted string follows the command, with nothing else. Always start with LEFT, CENTER, RIGHT, or another positioning keyword to stay on the current syntax and avoid that fallback behavior entirely.

SQL.PNO is only one of several system-maintained values you can reference inside a TTITLE or BTITLE command. The full set includes SQL.LNO, the current line number; SQL.RELEASE, the current Oracle Database release number; SQL.SQLCODE, the current error code; and SQL.USER, the current username, already used in an earlier lesson's BTITLE example. SQL.RELEASE deserves particular attention here: a report footer that prints the exact Oracle release it was generated against is genuinely useful documentation months later, when you or someone else is trying to figure out whether a report's formatting quirks are version-specific.

Page footers are defined identically to headers, just with the keyword BTITLE instead of TTITLE. Every clause covered above, LEFT, CENTER, RIGHT, SKIP, FORMAT, and the system variables, behaves exactly the same in a BTITLE command:

BTITLE CENTER "Confidential" SKIP 1

Two additional clauses are available for finer control than the three basic positions. BOLD marks the following text for bold formatting where your output medium supports it. COL n positions the next text item at an exact column number, useful when you want a title element to line up precisely with a specific data column rather than sitting at one of the line's edges or its center.

The center and right edge of a SQL*Plus page are based entirely on the current line width setting, not your printer's actual paper size. SQL*Plus defaults to 80 characters per line, which makes character 80 the right edge and character 40 the center for CENTER and RIGHT positioning purposes. If your report output is narrower or wider than that, set LINESIZE so your right-aligned and centered title text actually lines up with your real report width:

SET LINESIZE 54

With a narrower report, roughly 54 characters wide, setting LINESIZE 54 makes the title's right edge match the report's actual right edge:

COIN Database                                  Page  1
                 Database Object Report

Just as LINESIZE controls characters per line, PAGESIZE controls lines per page. The default is 14 lines, considerably shorter than you might expect for a report meant to be read on screen; increase it for longer pages, or decrease it further to force more frequent header reprints:

SET PAGESIZE 50

This matters directly for footers: before printing a BTITLE, SQL*Plus outputs enough blank lines to push the footer down to the bottom of the current page, based on wherever PAGESIZE says that bottom actually is. If PAGESIZE is too small, or your query output is taller than expected, footer placement can look genuinely wrong, seemingly floating in the middle of a page rather than sitting at the bottom. Choose a PAGESIZE that actually matches how you intend the report to paginate, and test with representative data rather than assuming the default will look right.

One setting worth knowing for the opposite situation: SET PAGESIZE 0 suppresses all headings, page breaks, titles, and the initial blank line entirely. This is the fastest way to strip a report down to raw, unformatted output, useful when a script's output is meant to be parsed by another program rather than read by a person, and none of the formatting covered in this lesson would help that downstream program at all.

For new reporting scripts, particularly ones meant for automation rather than a human reader, SQLcl's structured output formats are often the better choice over TTITLE and BTITLE formatting entirely. It is worth being precise about which of these commands actually require SQLcl, though, since they are not all the same:

SET MARKUP HTML ON
SET SQLFORMAT CSV
SET SQLFORMAT JSON

SET MARKUP HTML ON is a native SQL*Plus command, not a SQLcl-exclusive one; it works in plain SQL*Plus itself, generating HTML output directly. SET SQLFORMAT, by contrast, genuinely is exclusive to SQLcl, with no SQL*Plus equivalent at all; CSV and JSON output specifically require SQLcl rather than SQL*Plus. If you only need HTML output, SQL*Plus alone can do it; if you need CSV or JSON, SQLcl is required.

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