Managing Users Connectivity   «Prev  Next»

Lesson 11Page headers and footers
ObjectiveAdd page headers and footers to a report.

Oracle Report Page Headers Footers(How to add?)

To label the pages of your report, you can use headers and footers, which are called titles in SQL*Plus. The commands to place text at the top or bottom of a page are TTITLE (top title) and BTITLE (bottom title). Both, conveniently, use the same syntax, which is best explained by example. The following mouseover contains a TTITLE command for use with the object report. Move your mouse pointer across it for an explanation of each syntax element.

  1. TTITLE is the command that defines a page header. You can abbreviate it to TTI.
  2. Tells SQL*Plus to align subsequent text with the left edge of the page.
  3. This text will print as part of the first title line, and it will start on the left side of the page.
  4. The hyphen continues the TTITLE command to another line.
  5. Tells SQL*Plus to align subsequent text with the right edge of the page.
  6. Is a literal that will be printed in front of the page number.
  7. This format specification is applied to any numbers, in this case the page number, that subsequently appear in the page header.
  8. This is a special construct that SQL*Plus recognizes, and replaces with the actual page number.
  9. The hyphen continues the TTITLE command to another line.
  10. Tells SQL*Plus to advance one line.
  11. Tells SQL*Plus to center the text that follows.
  12. This is the report name, and it will be centered on the second title line.
  13. The hyphen continues the TTITLE command to another line.
  14. Tells SQL*Plus to advance six lines, in order to leave some space between the page heading and the column data.

Oracle Report Title Syntax
TTITLE commands are best interpreted as a series of instructions, reading from left to right. Whenever SQL*Plus advances to a new page, it executes those instructions to generate and display a title.
Never place text immediately after a TTITLE or BTITLE command. Doing so causes SQL*Plus to revert to an old version of the command, and the results will not be what you expect. cAlways lead off with a keyword such as LEFT, CENTER, or RIGHT. c
Page footers are defined the same as headers, except that the BTITLE keyword is used. All the other keywords are the same. You can put the same things at the bottom of a page that you can put at the top.

Centering, right-alignment, and LINESIZE

The center and right edge of a page according to SQL *Plus do not necessarily correspond to the size of the paper in your printer. SQL*Plus, by default, sets the size of a line to 80 characters, making the eightieth character space the right edge and the fortieth the center. If your paper is a different size or you simply want to use a smaller part of the page, you can use the SET LINESIZE command to specify how many characters wide you want a line to be for your report. Since the database object report is so narrow, only being about 54 columns wide, the centered part of the title will look better if you execute this command first:


This setting causes the right edge of the title to line up with the right edge of the report. This command, together with the TTITLE command shown in the mouseover, cwill result in a page title like this:
COIN DatabasePage    1
Database Object Report

Not shown here are the five blank lines that will appear between the title and the column headings.


Just as you can set the number of characters in a line, you can set the number of lines on a page. The SQL*Plus default is to print 24 lines per page. You can change this by using the SET PAGESIZE command. The following example sets the size to 50 lines:


The page size becomes especially important when you define page footers (BTITLE) because before it prints a footer, SQL*Plus will print enough blank lines to make the footer print at the bottom of the page.

Oracle Report Page Headers Footers - Exercise

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