Page 96
Listing 4.9 Continued
ROOSEVELT 8000 TRUMAN 11000 **** -------- avg 9250 sum 55500 POL CLINTON 15000 JOHNSON 12000 JOHNSON 7500 KENNEDY 11500 NIXON 12500 ROOSEVELT 10400 WILSON 9000 **** -------- avg 11128.571 sum 77900 FK_D LAST_NAME WAGES ---- --------------- --------- WEL ANTHONY 7000 CARTER 13000 HOOVER 10000 REAGAN 13500 ROOSEVELT 0 TAFT 8500 **** --------- avg 8666.6667 sum 52000 19 rows selected.
To modify the SQL*PLUS operating environment and other parameters that can affect your report, use the set command. It enables you to change settings that control items such as screen output, page width, line size, margin width, or word wrap. You can place these settings in the command file before the select clause or enter them directly in the buffer from the editor. They remain until you enter a new setting or the SQL*PLUS session is ended. Table 4.6 contains a listing of these settings.
Page 97
Table 4.6Set Command Modes and Values
Setting Name | Description |
array n |
This setting determines the number of rows SQL*PLUS fetches at one time. The n value represents the number of rows. The default is 20, and the range is 1 to 5000. The larger the size the greater the efficiency of the query; however, more memory will be needed. |
auto or autocommit |
This setting affects when SQL*PLUS commits changes to the database. It has three settings: off, on, and imm (or immediate). A setting of on or imm causes the commit to occur immediately after the execution of SQL commands. The default is off. |
buf or buffer setting |
The setting is used to change current buffers from the default SQL buffer to another or back to the default. |
concat symbol | Changes the symbol used to concatenate string values. |
copycommit n |
This setting commits rows to the destination database on a cycle of n batches of rows. Values range from 0 to 5000. |
def or define |
Defines the character used in indicate a substitution variable. The default is an `&'. The setting may also be changed to on or off. These settings determine whether SQL*PLUS will scan for a substitution variable. |
doc or document setting |
A setting of on allows the Document command to work. This command tells SQL*PLUS a block of documentation is beginning. The default is off. |
echo setting |
The on setting causes the sql commands to display to the screen as they are executed from a command file. The default is off. |
embedded setting |
The on setting allows a new report in a series of reports to begin anywhere on a page. The off setting forces the new report to start at the top of a new page. |
escape symbol |
The escape symbol may be changed from the default `\' with this setting. In addition, changing this setting to off disables this setting. |
Feed or feedback setting |
This setting determines when the "records selected" value displays. If the amount of records selected is greater than or equal to the specified value, the amount of records selected by the query displays. The default setting is `6' records. To turn off the display, use the off setting. flush setting. The off setting is used when a command file can be run without needing any display or interaction until it has completed. It enables the operating system to avoid sending output to the display. |
continues
Page 98
Table 4.6Continued
Setting Name |
Description |
hea or heading setting | The off setting shuts off column headings. The default is on. |
heads symbol |
This setting is used to change the default heading separator `|' to another symbol. The setting can be turned off and on. |
lin or linesize n |
This setting specifies the length of a line of output. Output longer than this line will wrap to the next line. The default is 80 characters. |
long n |
This setting determines the maximum width for displaying or copying long values. The value may be set from 1 to 32767. |
maxd or maxdata n |
This setting determines the maximum total row width that SQL*PLUS can process. The default and maximum value vary with the operating sytem. |
newp or newpage n |
This setting (n) specifies the number of blank lines to be printed between the bottom of one page and the top title of the next. A value of zero (0) sends a form feed at the top of each page. |
null text |
This setting enables you to substitute text for a null value when they are discovered. |
numf or numformat format[ormat] |
This setting enables you to specify the default number format. See Table 4.3 for a listing of formats. |
num n |
This setting changes the default width for number displays. The original default is 10 digits. |
Pages or pagesize n |
This setting determines the number of lines per page. The default is 14. |
pau or pause setting |
The on setting causes SQL*PLUS to wait for you to press enter before displaying the next page. The default is on. The setting may also be used to specify text that will be displayed during a pause. |
recsep setting |
This setting defines when a line of characters is printed. The each setting will print the characters after each line. The wrapped setting prints after the wrapped line. The off setting suppresses the printing. |
recsepchar symbol | Sets the character used for the recsep setting. |
scan setting |
This setting suppresses substitution variables that may be defined in the command file. The settings consist of on and off. |
Page 99
Setting Name |
Description |
show |
This settings causes SQL*PLUS to display the current and old value of the named set command. A setting of off suppresses this display. A settin of on will display the values. |
spa or space n |
This setting determines the number of spaces between columns in a row of the output. |
sqlc or sqlcase setting |
Converts all text in SQL commands or PL/SQL blocks before it is executed. You can use mixed, upper, or lower settings may be used to change the case. |
sqlco or sqlcontinue setting |
Changes the character(s) used in the editor for the continue line prompt. The default prompt is `>'. |
sqln or sqlnumber setting |
When this setting is on, each line of an SQL command will have a line number. When the setting is off, subsequent lines of the command will not have line numbers. The default is on. |
sqlpre or sqlprefix symbol | Changes the SQL*PLUS prefix character. |
sqlp or sqlprompt text |
Change the SQL*PLUS prompt from the default `SQL> to a new text string. |
Sqlt or sqlterminator symbol |
Changes the symbol that terminates a sql statement from `;' to a new value. The terminator can be turned off with the off setting. The on setting returns it to a semi-colon. |
suffix text} |
Changes the default file name extension the editor uses. The default is `sql'. |
tab setting |
The off setting causes SQL to use spaces in formatting columns and text on reports. The default setting is system dependent. The show tab command displays the setting. The on setting tells SQL*PLUS to use tabs rather than spaces. |
term or termout setting |
Setting this value to off suppresses the display of SQL*PLUS output on-screen. The default setting of on causes the output to display. |
ti or time[me] {off | on} |
A value of on causes the current time to display before each prompt command. The default value is off. |
timi or timing[ng] setting |
A setting of on shows timing statistics for each SQL command run. The default value off suppresses this display. |
trim or trimout setting |
A value of on trims blanks at the end of each displayed line rather than displaying them. The set tab on setting must be in effect. |
und or underline setting | This setting turns the underlining off and on. |
ver or verify setting |
A value of on causes SQL*PLUS to show the old and new values of a variable before executing the SQL in which they have been embedded. |