Previous | Table of Contents | Next

Page 83

CHAPTER 4

Formatting Reports in SQL*PLUS

As shown thus far, SQL*PLUS has the capability to query and acquire data. It also has powerful report writing features. In fact, SQL*PLUS is Oracle's first report writer. Even though it has been superseded by SQL*REPORTS, it is still an excellent tool for creating reports.

This chapter covers the format statements that enable the developer to create titles and footers, customize column headings, perform subtotals, and set environmental variables.

This chapter uses the same database used in Chapter 3. Installation instructions for the database are contained in Appendix D, "Practice Database Installation Instructions."

To format reports or queries, you enter special settings or commands prior to the select statement. You may enter these settings directly into the editor, or you may enter them in a command file. To then run the command file, use the start or @ symbol. The command file proves advantageous because your report is saved where it can be run repeatedly. Obtaining an acceptable format often takes many tries. Having the settings in a command file eases the development task.

Page 84

Creating Report Titles

To place a title format into the buffer, use the ttitle command. The entered setting displays at the top of each page of the report. After the command is entered, it remains in the buffer until a new ttitle command or the ttitle off command is entered. The latter command clears the title format from the buffer. It is important to clear the buffer because queries will continue to use the title setting even though it may not have been intended for the query.

You can enter this command directly into the buffer, or you can place it in a file containing all the other format commands. Ttitle is the first word of the command, telling Oracle that the developer is entering a title format statement. Table 4.1 contains some special formatting words you can use in the ttitle statement.

Table 4.1Ttitle Settings

Name Description
- (dash) This character indicates that the command will continue on
the following line.
` ' (single quotes) Literal text that will be included in the title is enclosed with
single quotes.
center Places the item following the keyword in the center of the line.
col n Places text following it at the specified column. The
column number is represented by the small n.
left Places the item following it on the left side of the line. This
is called left justification.
off Suppresses display defaults.
skip n Causes SQL*PLUS to skip the specified number of lines and
print the remainder of the title. The letter n represent the number
of lines to skip.
sql.pno Displays or prints the current page number.

Listing 4.1 illustrates some of the ttitle settings. It uses the left keyword to place the current date, which is placed in the command as a literal on the left side of the first line. The center keyword is used to justify the first line of the title in the center of the line. The skip 1 keyword tells SQL*PLUS to print the next part of the title on the next line. Because the entire command cannot be placed on one line, a dash (-) is placed at the end of the line. This tells the editor that you have not finished entering the complete command, and it is continued on the subsequent line. Notice that SQL*PLUS does not put a line number or the SQL> at the head of the line. It puts the > symbol, which means that SQL*PLUS considers instructions entered on this line a continuation of the previous line.

Page 85

Listing 4.1L_04_01.TXT—Using the Ttitle Command to Create a Report Title

SQL> ttitle left '17-AUG-97' center `LAST NAMES' skip 1 -
> left sql.pno center `AND FIRST NAMES' skip 1-
> col 45 `CONFIDENTIAL'
SQL> select last_name, first_name
  2  from employee;
17-AUG-97                                      LAST NAMES
        1                                  AND FIRST NAMES
                                            CONFIDENTIAL
LAST_NAME       FIRST_NAME
--------------- ---------------
TAFT            WILLIAM
ROOSEVELT       THEODORE
ANTHONY         SUSAN
ROOSEVELT       ELEANOR
COOLIDGE        CALVIN
JOHNSON         LYNDON
REAGAN          RONALD
BUSH            GEORGE
JOHNSON         ANDREW
CLINTON         WILLIAM
CARTER          JIMMY
FORD            GERALD
NIXON           RICHARD
KENNEDY         JOHN
EISENHOWER      DWIGHT
TRUMAN          HAROLD
ROOSEVELT       FRANKLIN
HOOVER          HERBERT
sysdate                                      LAST NAMES
        2                                  AND FIRST NAMES
                                            CONFIDENTIAL
LAST_NAME       FIRST_NAME
--------------- ---------------
WILSON          WOODROW

19 records selected.

The first setting placed on the continuation line in the preceding listing uses the left format keyword to left-justify the page number that will be printed as the result of placing sql.pno in the title. The second line of the title will be centered, and the literal "CONFIDENTIAL" will be printed on the following line beginning on column 45. By pressing Enter, you place the statement in the buffer because the second line does not contain a dash (-) character.

Creating Report Footers

The btitle command works the same as ttitle except that it formats the bottom of each report, known as the footer. The entered settings display at the bottom of each page of the report. This command uses the same format words and characters contained in Listing 4.1. You can enter

Page 86

this command directly into the buffer or place the command in a command file. The setting must be placed before the select statement. The command remains in memory until you use the btitle off command or a new btitle command supersedes it. Listing 4.2 demonstrates entering a btitle command into the buffer.

Listing 4.2 L_04_02.TXT—Using the Btitle Command to Create a Report Footer

SQL> btitle left `SEND TO Mr. Palinski'
SQL> select last_name, first_name
  2  from employee;
17-AUG-97                                      LAST NAMES
        1                                  AND FIRST NAMES
                                            CONFIDENTIAL
LAST_NAME       FIRST_NAME
--------------- ---------------
TAFT            WILLIAM
ROOSEVELT       THEODORE
ANTHONY         SUSAN
ROOSEVELT       ELEANOR
COOLIDGE        CALVIN
JOHNSON         LYNDON
REAGAN          RONALD
BUSH            GEORGE
JOHNSON         ANDREW
CLINTON         WILLIAM
CARTER          JIMMY
FORD            GERALD
NIXON           RICHARD
KENNEDY         JOHN
EISENHOWER      DWIGHT
TRUMAN          HAROLD
ROOSEVELT       FRANKLIN
SEND TO Mr. Palinski
17-AUG-97                                      LAST NAMES
        2                                  AND FIRST NAMES
                                            CONFIDENTIAL
LAST_NAME       FIRST_NAME
--------------- ---------------
HOOVER          HERBERT
WILSON          WOODROW
SEND TO Mr. Palinski

19 records selected.

Formatting Report Columns

As shown in Chapter 3, placing a column alias immediately following the column or expression in the select clause will change the default column heading. If the alias is lowercase and consists of one word, it is not enclosed by single quotation marks. If it is uppercase, mixed case, or consists of two or more words, the alias must be enclosed by double quotation marks. The alias

Previous | Table of Contents | Next