How to use BTITLE and TTITLE in Oracle

SQL*Plus isn’t known for its report-writing capabilities. But there
are a few tricks you can use to spiff up the output of your PL/SQL
queries. One of these tricks is setting automatic titles for your
output. This can be done with TTITLE (which sets the top title box)
and BTITLE (used to set the footer).

The format of the commands is:

TTITLE
BTITLE

The first parameter controls a variety of options for TTITLE and
BTITLE. These include:

COLUMN x – Goes to the xth column before printing text
TAB x – moves over x columns before printing text
LEFT, CENTER, RIGHT – Justifies the title text
SKIP x – Skips x lines
FORMAT – Applies the specified format mask to the text

Within the option, you can use any string of text
you want. You can also use the system variable sql.pno, which will
print the current page number. You can also put any SQL*Plus variable
within your string. Finally, specifying TTITLE or BTITLE with ON or
OFF will turn the title or footer on or off, respectively.

For example, let’s say you had a report that listed your grocery list.
You could set up headers and footers as follows:

Column today new_value today_var noprint
Select to_char(sysdate,’mm/dd/yyyy’) today from dual;
TTITLE RIGHT today_var SKIP 1 –
CENTER “Grocery List”
BTITLE CENTER “Page ” format 999 sql.pno

Notice how we created a variable to hold the current date for the
title. After running this bit of code, we now will have a title and
footer every time we run a SQL statement. For example, a sample
output could look like the following:

06/06/1999
Grocery List

ITEM QTY PRICE
——————– ———- ———-
Carrots 3 1.23
Cereal 1 3.44
Peanut Butter 1 2.56

Page 1