Page 83
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
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.TXTUsing 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.
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.TXTUsing 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.
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