SQL*Plus User's Guide and Reference

Contents Index Home Previous Next

Defining Page and Report Titles and Dimensions

The word page refers to a screenful of information on your display or a page of a spooled (printed) report. You can place top and bottom titles on each page, set the number of lines per page, and determine the width of each line.

The word report refers to the complete results of a query. You can also place headers and footers on each report and format them in the same way as top and bottom titles on pages.

Setting the Top and Bottom Titles and Headers and Footers

As you have already seen, you can set a title to display at the top of each page of a report. You can also set a title to display at the bottom of each page. The TTITLE command defines the top title; the BTITLE command defines the bottom title.

You can also set a header and footer for each report. The REPHEADER command defines the report header; the REPFOOTER command defines the report footer.

A TTITLE, BTITLE, REPHEADER or REPFOOTER command consists of the command name followed by one or more clauses specifying a position or format and a CHAR value you wish to place in that position or give that format. You can include multiple sets of clauses and CHAR values:

TTITLE position_clause(s) char_value position_clause(s) char_value ...
BTITLE position_clause(s) char_value position_clause(s) char_value ...
REPHEADER position_clause(s) char_value position_clause(s) char_value ...
REPFOOTER position_clause(s) char_value position_clause(s) char_value ...

The most often used clauses of TTITLE, BTITLE, REPHEADER and REPFOOTER are summarized in the following table. For descriptions of all TTITLE, BTITLE, REPHEADER and REPFOOTER clauses, see the discussions of TTITLE and REPHEADER[*].

Clause Example Description
COL n COL 72 Makes the next CHAR value appear in the specified column of the line.
SKIP n SKIP 2 Skips to a new line n times. If n is greater than 1, n-1 blank lines appear before the next CHAR value.
LEFT LEFT Left-aligns the following CHAR value.
CENTER CENTER Centers the following CHAR value.
RIGHT RIGHT Right-aligns the following CHAR value.
Table 4 - 2. Often-Used Clauses of TTITLE, BTITLE, REPHEADER and REPFOOTER
Example 4-17 Placing a Top and Bottom Title on a Page

To put titles at the top and bottom of each page of a report, enter

SQL> TTITLE CENTER -
>    'ACME WIDGET SALES DEPARTMENT PERSONNEL REPORT'
SQL> BTITLE CENTER 'COMPANY CONFIDENTIAL'

Now run the current query:

SQL> /

SQL*Plus displays the following output:

              ACME WIDGET SALES DEPARTMENT PERSONNEL REPORT
    DEPTNO ENAME             SAL
---------- ---------- ----------
        30 JAMES             950
        30 WARD             1250
        30 MARTIN           1250
        30 TURNER           1500
        30 ALLEN            1600
        30 BLAKE            2850



                             COMPANY CONFIDENTIAL

Example 4-18 Placing a Header on a Report

To put a report header on a separate page, and to center it, enter

SQL> REPHEADER PAGE CENTER 'ACME WIDGET'

Now run the current query:

SQL> /

SQL*Plus displays the following output on page one

              ACME WIDGET SALES DEPARTMENT PERSONNEL REPORT
                               ACME WIDGET








                           COMPANY CONFIDENTIAL

and the following output on page two

              ACME WIDGET SALES DEPARTMENT PERSONNEL REPORT
    DEPTNO ENAME             SAL
---------- ---------- ----------
        30 JAMES             950
        30 WARD             1250
        30 MARTIN           1250
        30 TURNER           1500
        30 ALLEN            1600
        30 BLAKE            2850



                             COMPANY CONFIDENTIAL

To suppress the report header without changing its definition, enter

SQL> REPHEADER OFF

Positioning Title Elements

The report in the preceding exercises might look more attractive if you give the company name more emphasis and place the type of report and the department name on either end of a separate line. It may also help to reduce the linesize and thus center the titles more closely around the data.

You can accomplish these changes by adding some clauses to the TTITLE command and by resetting the system variable LINESIZE, as the following example shows.

You can format report headers and footers in the same way as BTITLE and TTITLE using the REPHEADER and REPFOOTER commands.

Example 4-19 Positioning Title Elements

To redisplay the personnel report with a repositioned top title, enter the following commands:

SQL> TTITLE CENTER 'A C M E  W I D G E T' SKIP 1 -
>  CENTER ================ SKIP 1 LEFT 'PERSONNEL REPORT' -
>  RIGHT 'SALES DEPARTMENT' SKIP 2
SQL> SET LINESIZE 60
SQL> /

SQL*Plus displays the results:

                 A C M E  W I D G E T
                 ====================
PERSONNEL REPORT                        SALES DEPARTMENT

    DEPTNO ENAME             SAL
---------- ---------- ----------
        30 JAMES             950
        30 WARD             1250
        30 MARTIN           1250
        30 TURNER           1500
        30 ALLEN            1600
        30 BLAKE            2850
                      COMPANY CONFIDENTIAL

The LEFT, RIGHT, and CENTER clauses place the following values at the beginning, end, and center of the line. The SKIP clause tells SQL*Plus to move down one or more lines.

Note that there is no longer any space between the last row of the results and the bottom title. The last line of the bottom title prints on the last line of the page. The amount of space between the last row of the report and the bottom title depends on the overall page size, the number of lines occupied by the top title, and the number of rows in a given page. In the above example, the top title occupies three more lines than the top title in the previous example. You will learn to set the number of lines per page later in this chapter.

To always print n blank lines before the bottom title, use the SKIP n clause at the beginning of the BTITLE command. For example, to skip one line before the bottom title in the example above, you could enter the following command:

BTITLE SKIP 1 CENTER 'COMPANY CONFIDENTIAL'

Indenting a Title Element

You can use the COL clause in TTITLE or BTITLE to indent the title element a specific number of spaces. For example, COL 1 places the following values in the first character position, and so is equivalent to LEFT, or an indent of zero. COL 15 places the title element in the 15th character position, indenting it 14 spaces.

Exercise 4-20 Indenting a Title Element

To print the company name left-aligned with the report name indented five spaces on the next line, enter

SQL> TTITLE LEFT 'ACME WIDGET' SKIP 1 -
>  COL 6 'SALES DEPARTMENT PERSONNEL REPORT' SKIP 2

Now rerun the current query to see the results:

SQL> /
ACME WIDGET
     SALES DEPARTMENT PERSONNEL REPORT

    DEPTNO ENAME             SAL
---------- ---------- ----------
        30 JAMES             950
        30 WARD             1250
        30 MARTIN           1250
        30 TURNER           1500
        30 ALLEN            1600
        30 BLAKE            2850

                       COMPANY CONFIDENTIAL

Entering Long Titles

If you need to enter a title greater than 500 characters in length, you can use the SQL*Plus command DEFINE to place the text of each line of the title in a separate user variable:

SQL> DEFINE LINE1 = 'This is the first line...'
SQL> DEFINE LINE2 = 'This is the second line...'
SQL> DEFINE LINE3 = 'This is the third line...'

Then, reference the variables in your TTITLE or BTITLE command as follows:

SQL> TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 -
>    CENTER LINE3

Displaying the Page Number and other System-Maintained Values in Titles

You can display the current page number and other system-maintained values in your title by entering a system value name as a title element, for example:

TTITLE LEFT system-maintained_value_name

There are five system-maintained values you can display in titles, the most commonly used of which is SQL.PNO (the current page number). Refer to the TTITLE command[*] for a list of system-maintained values you can display in titles.

Example 4-21 Displaying the Current Page Number in a Title

To display the current page number at the top of each page, along with the company name, enter the following command:

SQL> TTITLE LEFT 'ACME WIDGET' RIGHT 'PAGE:' SQL.PNO SKIP 2

Now rerun the current query:

SQL> /

SQL*Plus displays the following results:

ACME WIDGET                               PAGE:         1

    DEPTNO ENAME             SAL
---------- ---------- ----------
        30 JAMES             950
        30 WARD             1250
        30 MARTIN           1250
        30 TURNER           1500
        30 ALLEN            1600
        30 BLAKE            2850


                      COMPANY CONFIDENTIAL

Note that SQL.PNO has a format ten spaces wide. You can change this format with the FORMAT clause of TTITLE (or BTITLE).

Example 4-22 Formatting a System-Maintained Value in a Title

To close up the space between the word PAGE: and the page number, re-enter the TTITLE command as shown:

SQL> TTITLE LEFT 'ACME WIDGET' RIGHT 'PAGE:' FORMAT 999 -
> SQL.PNO SKIP 2

Now rerun the query:

SQL> /

SQL*Plus displays the following results:

ACME WIDGET                                        PAGE:  1

    DEPTNO ENAME             SAL
---------- ---------- ----------
        30 JAMES             950
        30 WARD             1250
        30 MARTIN           1250
        30 TURNER           1500
        30 ALLEN            1600
        30 BLAKE            2850


                      COMPANY CONFIDENTIAL

Listing, Suppressing, and Restoring Page Title Definitions

To list a page title definition, enter the appropriate title command with no clauses:

TTITLE
BTITLE

To suppress a title definition, enter:

TTITLE OFF
BTITLE OFF

These commands cause SQL*Plus to cease displaying titles on reports, but do not clear the current definitions of the titles. You may restore the current definitions by entering

TTITLE ON
BTITLE ON

Displaying Column Values in Titles

You may wish to create a master/detail report that displays a changing master column value at the top of each page with the detail query results for that value below. You can reference a column value in a top title by storing the desired value in a variable and referencing the variable in a TTITLE command. Use the following form of the COLUMN command to define the variable:

COLUMN column_name NEW_VALUE variable_name

You must include the master column in an ORDER BY clause and in a BREAK command using the SKIP PAGE clause.

Example 4-23 Creating a Master/Detail Report

Suppose you want to create a report that displays two different managers' employee numbers, each at the top of a separate page, and the people reporting to the manager on the same page as the manager's employee number. First create a variable, MGRVAR, to hold the value of the current manager's employee number:

SQL> COLUMN MGR NEW_VALUE MGRVAR NOPRINT

Because you will display the managers' employee numbers in the title, you do not want them to print as part of the detail. The NOPRINT clause you entered above tells SQL*Plus not to print the column MGR.

Next, include a label and the value in your page title, enter the proper BREAK command, and suppress the bottom title from the last example:

SQL> TTITLE LEFT 'Manager: ' MGRVAR SKIP 2
SQL> BREAK ON MGR SKIP PAGE
SQL> BTITLE OFF

Finally, enter and run the following query:

SQL> SELECT MGR, ENAME, SAL, DEPTNO
  2  FROM EMP
  3  WHERE MGR IN (7698, 7839)
  3  ORDER BY MGR;

SQL*Plus displays the following output:

Manager:      7698

ENAME           SAL     DEPTNO
---------- -------- ----------
ALLEN          1600         30
WARD           1250         30
TURNER         1500         30
MARTIN         1250         30
JAMES           950         30

Manager:      7839

ENAME           SAL     DEPTNO
---------- -------- ----------
JONES          2975         20
BLAKE          2850         30
CLARK          2450         10

If you want to print the value of a column at the bottom of the page, you can use the COLUMN command in the following form:

COLUMN column_name OLD_VALUE variable_name

SQL*Plus prints the bottom title as part of the process of breaking to a new page--after finding the new value for the master column. Therefore, if you simply referenced the NEW_VALUE of the master column, you would get the value for the next set of detail. OLD_VALUE remembers the value of the master column that was in effect before the page break began.

Displaying the Current Date in Titles

You can, of course, date your reports by simply typing a value in the title. This is satisfactory for ad hoc reports, but if you want to run the same report repeatedly, you would probably prefer to have the date automatically appear when the report is run. You can do this by creating a variable to hold the current date.

To create the variable (in this example named _DATE), you can add the following commands to your SQL*Plus LOGIN file:

SET TERMOUT OFF
BREAK ON TODAY
COLUMN TODAY NEW_VALUE _DATE
SELECT TO_CHAR(SYSDATE, 'fmMonth DD, YYYY') TODAY
FROM DUAL;
CLEAR BREAKS
SET TERMOUT ON

When you start SQL*Plus, these commands place the value of SYSDATE (the current date) into a variable named _DATE. To display the current date, you can reference _DATE in a title as you would any other variable.

The date format model you include in the SELECT command in your LOGIN file determines the format in which SQL*Plus displays the date. See your Oracle7 Server SQL Language Reference Manual for more information on date format models. For more information about the LOGIN file, see "Modifying Your LOGIN File"[*].

You can also enter these commands interactively at the command prompt; see COLUMN[*] for an example.

Setting Page Dimensions

Typically, a page of a report contains the number of blank line(s) set in the NEWPAGE variable of the SET command, a top title, column headings, your query results, and a bottom title. SQL*Plus displays a report that is too long to fit on one page on several consecutive pages, each with its own titles and column headings. The amount of data SQL*Plus displays on each page depends on the current page dimensions.

The default page dimensions used by SQL*Plus are shown below:

You can change these settings to match the size of your computer screen or, for printing, the size of a sheet of paper.

You can change the page length with the system variable PAGESIZE. For example, you may wish to do so when you print a report, since printed pages are customarily 66 lines long.

To set the number of lines between the beginning of each page and the top title, use the NEWPAGE variable of the SET command:

SET NEWPAGE number_of_lines

If you set NEWPAGE to zero, SQL*Plus skips zero lines and displays and prints a formfeed character to begin a new page. On most types of computer screens, the formfeed character clears the screen and moves the cursor to the beginning of the first line. When you print a report, the formfeed character makes the printer move to the top of a new sheet of paper, even if the overall page length is less than that of the paper.

To set the number of lines on a page, use the PAGESIZE variable of the SET command:

SET PAGESIZE number_of_lines

You may wish to reduce the linesize to center a title properly over your output, or you may want to increase linesize for printing on wide paper. You can change the line width using the LINESIZE variable of the SET command:

SET LINESIZE number_of_characters

Example 4-24 Setting Page Dimensions

To set the page size to 66 lines, clear the screen (or advance the printer to a new sheet of paper) at the start of each page, and set the linesize to 32, enter the following commands:

SQL> SET PAGESIZE 66
SQL> SET NEWPAGE 0
SQL> SET LINESIZE 32

Now enter and run the following commands to see the results:

SQL> TTITLE CENTER 'ACME WIDGET PERSONNEL REPORT' SKIP 1 -
> CENTER '10-JAN-89' SKIP 2
SQL> COLUMN DEPTNO HEADING DEPARTMENT
SQL> COLUMN ENAME HEADING EMPLOYEE
SQL> COLUMN SAL FORMAT $99,999 HEADING SALARY
SQL> SELECT DEPTNO, ENAME, SAL
  2  FROM EMP
  3  ORDER BY DEPTNO;

SQL*Plus displays a formfeed followed by the query results:

  ACME WIDGET PERSONNEL REPORT
              10-JAN-89

DEPARTMENT EMPLOYEE       SALARY
---------- ---------- ----------
        10 CLARK          $2,450
        10 KING           $5,000
        10 MILLER         $1,300
        20 SMITH            $800
        20 ADAMS          $1,100
        20 FORD           $3,000
        20 SCOTT          $3,000
        20 JONES          $2,975
        30 ALLEN          $1,600
        30 BLAKE          $2,850
        30 MARTIN         $1,250
        30 JAMES            $950
        30 TURNER         $1,500
        30 WARD           $1,250

Now reset PAGESIZE, NEWPAGE, and LINESIZE to their default values:

SQL> SET PAGESIZE 24
SQL> SET NEWPAGE 1
SQL> SET LINESIZE 80

To list the current values of these variables, use the SHOW command:

SQL> SHOW PAGESIZE
pagesize 24
SQL> SHOW NEWPAGE
newpage 1
SQL> SHOW LINESIZE
linesize 80

Through the SQL*Plus command SPOOL, you can store you query results in a file or print them on your computer's default printer.

Sending Results to a File

To store the results of a query in a file--and still display them on the screen--enter the SPOOL command in the following form:

SPOOL file_name

SQL*Plus stores all information displayed on the screen after you enter the SPOOL command in the file you specify.

Contents Index Home Previous Next