Previous | Table of Contents | Next

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.

Setting Up the Environment

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.

Previous | Table of Contents | Next