Changes the characteristics of SQL terminal sessions. You can
control the:
o Currency indicator to be displayed for output
o Display format for date values, time values, or both
o Default path name in the data dictionary
o Digit separator to be displayed for output
o Number of statements to be included in the editing buffer when
you type EDIT *
o Language to be used for month abbreviations, and so on, in
date and time input and display
o Length of lines to be displayed for output
o Page length for HELP display
o File in which the session is recorded
o Number of rows output, the number of seconds allowed per query
compilation and execution, or the amount of CPU time expended
for each query compilation and execution
o Character used to display the radix point in output
o Display of statements from a command file
o Display of warning messages about deprecated features
o Display of warning messages about nonstandard syntax
o Continue character
1 – Environment
You can use these SET statements in interactive SQL only.
2 – Format
(B)0[m
[1;4mSET[m[1m w> [1;4mCURRENCY[m [1;4mSIGN[m[1m currency-char qqqqqqqqqqqqqqqqqqqqqqwq> [m
[1m t> set-date-format qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1mt> [1;4mCONTINUE[m[1m [1;4mCHARACTER[m[1m continue-char qqqqqqqqqqqqqqqqqu[m
[1m x> [1;4mDICTIONARY[m[1m qq> <path-name> qqqqqqqqqqqqqqqqqqqqqqqu [m
[1m t> [1;4mDIGIT[m[1m [1;4mSEPARATOR[m[1m digit-sep-char qqqqqqqqqqqqqqqqqqqu [m
[1m t> set-edit qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m t> [1;4mEXECUTE[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m t> [1;4mNOEXECUTE[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m t> [1;4mLANGUAGE[m[1m language-name qqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m t> [1;4mLINE[m[1m [1;4mLENGTH[m[1m qq> <n> qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m t> set-output qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1mt> [1;4mPAGE[m[1m [1;4mLENGTH[m[1m <n> qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m [1m [m
[1m t> [1;4mRADIX[m[1m POINT radix-char qqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m t> [1;4mVERIFY[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m t> [1;4mNOVERIFY[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m t> set-warning qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m t> set-flagger qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m m> sql-plus-options qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m [m [1m [m
(B)0[m[1mset-date-format= [m
[1m [m
[1mqqqq> [1;4mDATE[m[1m [1;4mFORMAT[m[1m w> [1;4mDATE[m[1m <date-number> wqqqqqqqqqqqqqqqqqqqqqqqwwq> [m
[1m x m> , [1;4mTIME[m[1m <time-number> jx [m
[1m m> [1;4mTIME[m[1m <time-number> wqqqqqqqqqqqqqqqqqqqqqqqwj [m
[1m m> , [1;4mDATE[m[1m <date-number> j [m
[1m [m
(B)0[m[1mset-edit= [m
[1m [m
[1mqq> [1;4mEDIT[m[1m qqqwqq> [1;4mKEEP[m[1m qqq> <n> wqq> [m
[1m tqq> [1;4mNOKEEP[m[1m qqqqqqqu [m
[1m mqq> [1;4mPURGE[m[1m qqqqqqqqj [m
[1m [m
(B)0[m[1mset-output= [m
[1m [m [1m [m
[1mqqwqq>[m [1;4mLOGFILE[m[1m qqwqqqqqqqqqqqqqqqqqqqqqqqqwqqqqq> quoted-filespec qwqqq>[m
[1mx[m [1mmq> (logfile-options) qqqj[m [1mx[m
[1mtqq> [1;4mOUTPUT[m[1m qqqqwqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m
[1m x [m [1m mqq> <file-spec> qj [m [1mx[m
[1m tqq> [1;4mNOLOGFILE[m [1mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m
[1mmqq>[m [1;4mNOOUTPUT[m [1mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj[m
(B)0[m[1mlogfile-options = [m
[1m [m
[1m qqwq> [1;4mECHO[m [1mqqqqqqqqqwqq>[m
[1mmq>[m [1;4mNOECHO[m [1mqqqqqqqqj[m
(B)0[m[1mset-warning= [m
[1m [m
[1mqqqq> [1;4mWARNING[m[1m qwq> [1;4mDEPRECATE[m[1m qqqwqqqqq> [m
[1m mq> [1;4mNODEPRECATE[m[1m qj [m
[1m [m
(B)0[m[1mset-flagger = [m
[1m [m
[1mqqq> [1;4mFLAGGER[m[1m qqwq> [1;4mON[m[1m qqqqqqqqqqqqqqqqqqqqqqqqwqwq> [m
[1m tq> [1;4mSQL89[m[1m qqqqqqqqwqwqqqqqqqqwqj x [m
[1m tq> [1;4mSQL92_ENTRY[m[1m qqu tq> ON qqu x [m
[1m tq> [1;4mMIA[m[1m qqqqqqqqqqj mq> OFF qj x [m
[1m mq> [1;4mOFF[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m [1msql-plus-options = [m
[1m [m
[1mqwq>[m [1;4mECHO[m[1m qqqqqwqwq> [1;4mON[m [1mqqwqqqqqqqqqqqqwq>[m
[1m tq> [1;4mHEADING[m [1m qu mq> [1;4mOFF[m[1m qj [m [1m [m [1mx[m
[1m tq> [1;4mTIMING[m[1m qqj [m [1m [m [1mx[m
[1mtq>[m [1;4mFEEDBACK[m [1mqwq> [1;4mON[m [1mqqqqqqqqqqqqqqqwqu[m
[1mx[m [1mtq>[m [1;4mOFF[m [1mqqqqqqqqqqqqqqu[m [1mx[m
[1mx[m [1mmq>[m [1mnumeric-literal[m [1mqqj[m [1mx[m
[1mtq>[m [1;4mLINESIZE[m [1mqwq>[m [1mnumeric-literal[m [1mqqqqu[m
[1mtq>[m [1;4mPAGESIZE[m [1mqj[m [1mx[m
[1m mq> [1;4mNULL[m[1m qwqqqqqqqqqqqqqqqqqqwqqqqqqqqj[m
[1mm> literal-string qj[m
3 – Arguments
3.1 – ALIAS
For information on SET ALIAS, type HELP SET_ALIAS.
3.2 – ALL_CONSTRAINTS
For information on SET ALL CONSTRAINTS, type HELP SET_ALL_
CONSTRAINTS.
3.3 – ANSI
For information on SET ANSI, type HELP SET_ANSI.
3.4 – CATALOG
For information on SET CATALOG, type HELP SET_CATALOG.
3.5 – CHARACTER_LENGTH
For information on SET CHARACTER LENGTH, type HELP SET_CHARACTER_
LENGTH.
3.6 – CONNECT
For information on SET CONNECT, type HELP SET_CONNECT.
3.7 – Control
For information on SET Control, type HELP SET_Control.
3.8 – CONTINUE_CHARACTER
Defines the continuation character for interactive SQL. By
selecting a seldom used character the database administrator
can avoid problems with the minus sign to use a continuation
character in scripts.
3.9 – CURRENCY_SIGN
Specifies the currency indicator to be displayed in output.
(SQL produces currency indicators in output when you specify the
dollar sign ($) edit string for the column. See the DATATRIEVE
HELP topic for more information on edit strings.)
If you do not specify an alternate character, the default is
either the dollar sign ($) or the value specified by the logical
name SYS$CURRENCY.
3.10 – DATE date number
Specifies the display format for date values.
You must enter a number for the date-number argument. This number
corresponds to numbers in the date format logical names listed in
tables in the OpenVMS run-time library documentation.
For example, LIB$DATE_FORMAT_006 is one of the logical names in
the table. The logical name specifies the format in which the
eighth day of May in the year 1957 would be displayed as 8 May
57. Note that the latter part of the logical name is the number
006.
If you wanted to specify the 8 May 57 format using the SET DATE
FORMAT statement, you would use the numeric part of the LIB$DATE_
FORMAT_006 logical name, 6. You do not have to enter any leading
zeros that the number might have.
If you do not specify a date format, the default is dd-mmm-yyyy.
3.11 – DATE_FORMAT
Specifies the display format for either date values, time values,
or both.
You must specify a numeric argument with the DATE and TIME
portions of the SET DATE FORMAT statement. This numeric argument
is the same as the numeric portion of certain OpenVMS Run-Time
Library formats. The formats are documented in the OpenVMS run-
time library documentation. (This statement only accepts numbers
that reference OpenVMS format date and time logical names; it
does not support the ANSI/ISO date and time data types.)
The SET DATE FORMAT DATE and SET DATE FORMAT TIME statements
change only the output for the date or time formats. If you want
to change the input format, use the logical name LIB$DT_INPUT_
FORMAT. You must run the command procedure SYS$MANAGER:LIB$DT_
STARTUP.COM before using any of the run-time library date-time
routines for input or output formats other than the default. The
LIB$DT_STARTUP.COM procedure also defines spellings for date and
time elements in languages other than English. See the OpenVMS
run-time library documentation for more information on LIB$DT_
INPUT_FORMAT.
3.12 – DEFAULT_CHARACTER_SET
For information on SET DEFAULT CHARACTER SET, type HELP SET_
DEFAULT_CHARACTER_SET.
3.13 – DEFAULT_DATE_FORMAT
For information on SET DEFAULT DATE FORMAT, type HELP SET_
DEFAULT_DATE_FORMAT.
3.14 – DIALECT
For information on SET DIALECT, type HELP SET_DIALECT.
3.15 – DICTIONARY
Changes your default repository path name to the path name you
specify.
3.16 – DIGIT_SEPARATOR
Changes the output displaying the digit separator to the
specified character. The digit separator is the symbol that
separates groups of three digits in values greater than 999. For
example, the comma is the digit separator in the number 1,000.
(SQL produces digit separators in output when you specify the
comma (,) edit string for the column. See the DATATRIEVE HELP
topic for more information on edit strings.)
You must enclose the digit-sep-char argument within single
quotation marks.
If you do not specify an alternate character, the default is
either the comma (,) or the value specified by the logical name
SYS$DIGIT_SEP.
3.17 – EDIT
Controls the size of the editing buffer that you create when you
use the EDIT statement with a wildcard as the argument.
o SET EDIT KEEP n
Tells SQL to save the previous n statements. For example,
assume you have specified SET EDIT KEEP 5. When you type EDIT
*, SQL places the previous five statements in the editing
buffer. The number you specify with SET EDIT KEEP is the
maximum number of statements you can recall with the EDIT
statement. The default is 20.
o SET EDIT NOKEEP
This statement is equivalent to SET EDIT KEEP 0. If you use
this form of the statement and you type EDIT or EDIT *, your
editing buffer will be empty. This form of the statement saves
system resources when you are running command files rather
than an interactive process.
o SET EDIT PURGE
This statement retains the value of the KEEP parameter but
purges all previous statements. As with SET EDIT NOKEEP, if
you use the SET EDIT PURGE statement and then EDIT or EDIT *,
your editing buffer will be empty. Unlike the SET EDIT NOKEEP
statement, however, SET EDIT PURGE causes SQL to accumulate
subsequent statements to place in the editing buffer when you
issue EDIT statements later in the interactive session.
3.18 – execute-clause
Syntax options:
EXECUTE
NOEXECUTE
NO EXECUTE
Instructs SQL whether to execute the data manipulation statements
you issue in an interactive SQL session.
You can use the NOEXECUTE option in conjunction with the
SET FLAGS to examine the estimated cost and access strategy
associated with a query. If you specify SET NOEXECUTE, SQL
displays the access strategies without executing the query. SQL
also allows you to specify NO EXECUTE (as two words); this has
the same meaning as NOEXECUTE.
If you do not specify EXECUTE or NOEXECUTE, the default is
EXECUTE.
The SET TRANSACTION statement is not executed when SET NO EXECUTE
is active. Start or declare a transaction prior to using SET NO
EXECUTE.
3.19 – FLAGGER_OFF
Disables all previously set flaggers indicating nonstandard
syntax. This is the default.
3.20 – FLAGGER
Syntax options:
FLAGGER ON
FLAGGER SQL89
FLAGGER SQL92_ENTRY
FLAGGER MIA
Controls the output of informational messages that indicate
nonstandard syntax, that is, extensions to the ANSI/ISO standard
syntax or the MIA standard syntax.
If you specify SET FLAGGER ON, which is the same as specifying
SET FLAGGER SQL92_ENTRY ON, SQL sends you an informational
message if you issue a subsequent interactive SQL statement that
contains syntax that is an extension to the ANSI/ISO standard.
If you specify SET FLAGGER MIA ON, SQL sends you an informational
message if you issue a subsequent interactive SQL statement that
contains syntax that is an extension to the MIA standard.
The flaggers are independent of each other and any combination of
flaggers can be set at one time.
The default is FLAGGER OFF if you do not explicitly set a flagger
on.
3.21 – FLAGS
For information on SET FLAGS, type HELP SET_FLAGS.
3.22 – HOLD_CURSORS
For information on SET HOLD CURSORS, type HELP SET_HOLD_CURSORS.
3.23 – IDENTIFIER_CHARACTER_SET
For information on SET IDENTIFIER CHARACTER SET, type HELP SET_
IDENTIFIER_CHARACTER_SET.
3.24 – KEYWORD_RULES
For information on SET KEYWORD RULES, type HELP SET_KEYWORD_
RULES.
3.25 – FEEDBACK
SET FEEDBACK ON is a synonym for the SQL SET DISPLAY NO ROW
COUNTER statement. SQL data manipulation statements such as
SELECT, DELETE, UPDATE, and INSERT will display the number of
affected rows. SET FEEDBACK n sets a limit value which turns on
feedback only if more than 'n' rows are displayed. SET FEEDBACK
0 is synonymous with SET FEEDBACK ON. SET FEEDBACK OFF is a
synonym for the SQL SET DISPLAY ROW COUNTER statement. SQL data
manipulation statements no longer display the count of affected
rows.
SQL> set feedback 2
SQL> select * from work_status;
STATUS_CODE STATUS_NAME STATUS_TYPE
0 INACTIVE RECORD EXPIRED
1 ACTIVE FULL TIME
2 ACTIVE PART TIME
3 rows selected
SQL> set feedback 4
SQL> select * from work_status;
STATUS_CODE STATUS_NAME STATUS_TYPE
0 INACTIVE RECORD EXPIRED
1 ACTIVE FULL TIME
2 ACTIVE PART TIME
3.26 – LANGUAGE
Specifies the language to be used for translation of month
names and abbreviations in date and time input and display. The
language-name argument also determines the translation of other
language-dependent text, such as the translation for the date
literals YESTERDAY, TODAY, and TOMORROW.
If you do not specify a language, the default is the language
specified by the logical name SYS$LANGUAGE. If you require
different language spellings, you must define the logical name
SYS$LANGUAGES in addition to SYS$LANGUAGE. You must run the
command procedure SYS$MANAGER:LIB$DT_STARTUP.COM after defining
SYS$LANGUAGES. For example:
$ DEFINE SYS$LANGUAGES FRENCH, GERMAN, SPANISH
$ RUN SYS$MANAGER:LIB$DT_STARTUP.COM
$ SHOW LOGICAL SYS$LANGUAGES
"SYS$LANGUAGES" = "FRENCH" (LNM$SYSTEM_TABLE)
$ SHOW LOGICAL SYS$LANGUAGE
"SYS$LANGUAGE" = "ENGLISH" (LNM$SYSTEM_TABLE)
If you do not define SYS$LANGUAGES, all translation routines
default to English. See the OpenVMS run-time library
documentation for more information on LIB$DT_STARTUP.COM.
The SET LANGUAGE statement does not affect the collating
sequences used for sorting and comparing data. The CREATE
COLLATING SEQUENCE statement specifies alternate collating
sequences.
3.27 – LINE LENGTH n LINESIZE n
Specifies an alternate line length for SQL output.
You must enter a number n to designate the line length. The
number n can be any number up to 65535 octets.
You can use the SET LINE LENGTH (or SET LINESIZE) statement to
specify an alternate width for output that you are sending to a
file or to an alternate output device.
3.28 – LITERAL_CHARACTER_SET
For information on SET LITERAL CHARACTER SET, type HELP SET_
LITERAL_CHARACTER_SET.
3.29 – LOGFILE quoted filespec
This statement allows the executing SQL script to save output to
an OpenVMS file.
Output from interactive SQL will be written to the file-spec
specified. If the "ECHO" logfile-option is used, in addition
to writing the output to the designated file, all commands
and errors generated by interactive SQL are also written to
SYS$OUTPUT. If the "NOECHO" logfile-option is used, output to
SYS$OUTPUT is disabled. All commands and errors generated by
interactive SQL are only written to the output file.
The SET LOGFILE is functionally equivalent to the SET OUTPUT
statement.
A SET LOGFILE command that does not specify a file is equivalent
to SET NOLOGFILE.
3.30 – NAMES
For information on SET NAMES, type HELP SET_NAMES.
3.31 – NATIONAL_CHARACTER_SET
For information on SET NATIONAL CHARACTER SET, type HELP SET_
NATIONAL_CHARACTER_SET.
3.32 – NOLOGFILE
Closes the current output file specified by a prior SET LOGFILE
(or SET OUTPUT command).
3.33 – NOOUTPUT
Suspends writing to the output file.
3.34 – NOVERIFY
Does not display indirect command files. The default setting is
the setting currently in effect for DCL commands. If you have
not explicitly changed the DCL setting to VERIFY, the default is
NOVERIFY.
3.35 – OPTIMIZATION_LEVEL
For information on SET OPTIMIZATION LEVEL, type HELP SET_
OPTIMIZATION_LEVEL.
3.36 – OUTPUT file spec
Names the target file for output. The default file extension is
.lis.
If you specify OUTPUT with a file name, SQL writes its output
to a log file that you specify. The log file contains both
statements and results. If you issue a SET OUTPUT statement,
output is also written to standard output which is usually the
terminal.
If you specify OUTPUT without a file name, SQL suspends writing
output to a log file, if any, and writes the output to the
standard output. In other words, the SET OUTPUT statement without
a file name is equivalent to the SET NOOUTPUT statement.
SQL displays certain items (such as the headings produced by
the SHOW statement) in boldface type on your terminal screen. In
log files, however, the boldface items are surrounded by escape
characters. You can ignore these escape characters, edit them
out of your log file, or set your terminal so that SQL does not
display characters in boldface type.
If you disable boldface type using the following DCL command,
your log file will not contain escape characters:
$ SET TERM/NOANSI_CRT
3.37 – PAGE LENGTH n PAGESIZE n
Sets the size of a page in SQL help.
The following notes apply to the PAGE LENGTH (or PAGESIZE)
clause:
o The integer value must be a value between 10 and 32767.
o SET PAGE LENGTH (or SET PAGESIZE) can be used to effectively
disable the paging performed by help by setting the length to
a high value such as 32000.
o The page length is automatically set upon entry to interactive
SQL and is based on the OpenVMS terminal setting for this
session.
o The SHOW DISPLAY command can be used to view the currently
defined page length.
3.38 – QUOTING_RULES
For information on SET QUOTING RULES, type HELP SET_QUOTING_
RULES.
3.39 – RADIX_POINT
Changes the output displaying the radix point to the specified
character. The radix point is the symbol that separates units
from decimal fractions. For example, in the number 98.6, the
period is the radix point.
You must enclose the radix-char argument within single quotation
marks.
If you do not specify an alternate character, the default is
either the period (.) or the value specified by the logical name
SYS$RADIX_POINT.
3.40 – SCHEMA
For information on SET SCHEMA, type HELP SET_SCHEMA.
3.41 – sql-plus-options
These statements are provided for use with SQL*Plus scripts that
are run against Oracle Rdb.
Table 1-5 Supported SQL*Plus SET statements
SQL*Plus command Equivalent Oracle Rdb statement
SET ECHO ON SET VERIFY
SET ECHO OFF SET NOVERIFY
SET HEADING ON SET DISPLAY QUERY HEADER
SET HEADING OFF SET DISPLAY NO QUERY HEADER
SET FEEDBACK ON SET DISPLAY ROW COUNTER
SET FEEDBACK OFF SET DISPLAY NO ROW COUNTER
SET NULL SET DISPLAY DEFAULT NULL STRING
SET NULL 'literal' SET DISPLAY NULL STRING 'literal'
3.42 – TIME time number
Specifies the display format for time values.
You must enter a number for the time-number argument. This number
corresponds to numbers in the time-format logical names listed in
tables in the OpenVMS run-time library documentation.
For example, the table contains the logical name
LIB$TIME_FORMAT_020. The logical name specifies the format in
which the eighth hour, fourth minute, and thirty-second second of
a day would be displayed as 8 h 4 min 32 s. Note that the latter
part of the logical name is the number 020.
If you wanted to specify the 8 h 4 min 32 s format for the SQL
SET DATE FORMAT TIME statement, you would use the numeric part
of the LIB$TIME_FORMAT_020 logical name, 20. You do not have to
enter any leading zeros that the number might have.
If you do not specify a time format, the default is hh:mm:ss.cc.
3.43 – TIMING
The SET TIMING statement enables a single line report of used CPU
and Elapsed time for each successful SQL statement or command.
SQL> start transaction;
SQL> set timing on;
SQL> select count(*)
cont> from employees
cont> inner join job_history using (employee_id)
cont> inner join salary_history using (employee_id)
cont> inner join departments using (department_code)
cont> inner join jobs using (job_code)
cont> left outer join resumes using (employee_id)
cont> left outer join degrees using (employee_id)
cont> left outer join colleges using (college_code)
cont>
cont> ;
3871
1 row selected
Timing: Elapsed: 0 00:00:00.82 Cpu: 0 00:00:00.16
SQL> set timing off;
SQL> commit;
3.44 – TRANSACTION
For information on SET TRANSACTION, type HELP SET_TRANSACTION.
3.45 – VERIFY
Displays indirect command files at your terminal as you run them.
3.46 – VIEW_UPDATE_RULES
For information on SET VIEW UPDATE RULES, type HELP SET_VIEW_
UPDATE_RULES.
3.47 – WARNING
Syntax options:
WARNING DEPRECATE
WARNING NODEPRECATE
Specifies whether or not interactive SQL displays diagnostic
messages when you issue statements containing obsolete SQL
syntax. Deprecated or obsolete syntax is syntax that was allowed
in previous versions of SQL but has been changed. Oracle Rdb
recommends that you avoid using such syntax because it may not
be supported in future versions. By default, SQL displays a
warning message after any statement containing obsolete syntax
(SET WARNING DEPRECATE).
If you specify SET WARNING NODEPRECATE, SQL does not display any
messages about obsolete syntax.
4 – Examples
Example 1: Using the SET statement to set up terminal session
characteristics
Using the SET statement as follows, you can set up the
characteristics of your terminal session:
SQL> --
SQL> -- You can put the SET statements in your sqlini file, which sets up
SQL> -- your SQL session.
SQL> --
SQL> SET OUTPUT 'LOG.LIS'
SQL> SET DICTIONARY 'CDD$TOP.DEPT3'
SQL> SET EDIT KEEP 10
SQL> --
SQL> ATTACH 'ALIAS PERS FILENAME personnel';
SQL> SHOW ALIAS
Alias PERS:
Rdb database in file personnel
SQL> EXIT
In the preceding example, the statements set up the
characteristics, as follows:
o The SET OUTPUT statement opens a file called LOG.LIS in the
current default path name. From this point on, all the input
and output, including error messages, appear in this file.
The following example shows what is written to the log file
LOG.LIS:
SET DICTIONARY 'CDD$TOP.DEPT3'
SET EDIT KEEP 10
--
ATTACH 'ALIAS PERS FILENAME personnel';
SHOW ALIAS
Alias PERS:
Rdb database in file personnel
EXIT
o The SET DICTIONARY statement changes the default repository
path name.
o The SET EDIT KEEP statement specifies that you get the 10
previous statements in the editing buffer when you type EDIT
*.
o The ATTACH statement attaches to the personnel database and
declares the alias PERS for that database.
o The SHOW ALIAS statements tell the user which alias is
declared.
Example 2: SET CURRENCY SIGN and SET DIGIT SEPARATOR statements
The following example uses the SET DIGIT SEPARATOR statement
to show the behavior of the SET CURRENCY SIGN and SET DIGIT
SEPARATOR statements when used with edit strings:
SQL> --
SQL> -- This example shows the edit string 'ZZZ,ZZZ',
SQL> -- which specifies the comma as the default digit separator.
SQL> --
SQL> ALTER TABLE SALARY_HISTORY -
cont> ALTER SALARY_AMOUNT EDIT STRING 'ZZZ,ZZZ';
SQL> SELECT SALARY_AMOUNT FROM SALARY_HISTORY;
SALARY_AMOUNT
26,291
51,712
26,291
50,000
.
.
.
SQL> --
SQL> -- Now use the SET DIGIT SEPARATOR statement to specify that
SQL> -- the period will be the digit separator instead of
SQL> -- the comma.
SQL> --
SQL> SET DIGIT SEPARATOR '.'
SQL> SELECT SALARY_AMOUNT FROM SALARY_HISTORY;
SALARY_AMOUNT
26.291
51.712
26.291
50.000
.
.
.
Example 3: Using the internationalization features of the SET
statement
The following example shows how to use the various SET statements
to internationalize your applications:
SQL> --
SQL> -- This first statement specifies the dollar sign
SQL> -- as the currency indicator. It does this by using
SQL> -- the edit string '$(9).99'.
SQL> --
SQL> ALTER TABLE SALARY_HISTORY -
cont> ALTER SALARY_AMOUNT EDIT STRING '$(9).99';
cont> SELECT SALARY_AMOUNT FROM SALARY_HISTORY;
SALARY_AMOUNT
$26291.00
$51712.00
$26291.00
$50000.00
.
.
.
SQL> --
SQL> -- The SET CURRENCY statement now changes the currency
SQL> -- indicator to the British pound sign, £. Notice
SQL> -- the changed output.
SQL> --
SQL> SET CURRENCY SIGN '£'
SQL> SELECT SALARY_AMOUNT FROM SALARY_HISTORY;
SALARY_AMOUNT
£26291.00
£51712.00
£26291.00
£50000.00
£11676.00
.
.
.
SQL> --
SQL> -- The next examples show the SET DATE FORMAT statement.
SQL> --
SQL> -- The SET DATE FORMAT statement will not override input
SQL> -- and output formats that you have specified with an edit
SQL> -- string. The following SET DATE FORMAT examples use the
SQL> -- SALARY_START and SALARY_END columns. The SALARY_START
SQL> -- and SALARY_END columns are defined by the domain
SQL> -- DATE_DOM, which uses the edit string 'DD-MMM-YYY'.
SQL> -- Thus, to test the SET DATE FORMAT statement, you must
SQL> -- first remove the edit string from the DATE_DOM domain
SQL> -- using the following ALTER DOMAIN statement:
SQL> --
SQL> ALTER DOMAIN DATE_DOM NO EDIT STRING;
SQL> --
SQL> -- The next statement inserts a row with time information.
SQL> -- The subsequent SET DATE FORMAT statements will use this row:
SQL> --
SQL> INSERT INTO SALARY_HISTORY
cont> -- list of columns:
cont> (EMPLOYEE_ID,
cont> SALARY_AMOUNT,
cont> SALARY_START,
cont> SALARY_END)
cont> VALUES
cont> -- list of values:
cont> ('88339',
cont> '22550',
cont> '14-NOV-1967 08:30:00.00',
cont> '25-NOV-1988 16:30:00.00')
cont> ;
1 row inserted
SQL> --
SQL> -- Using the row that was just inserted, the following statement
SQL> -- shows the default date and time output:
SQL> --
SQL> SELECT SALARY_START, SALARY_END FROM SALARY_HISTORY-
cont> WHERE EMPLOYEE_ID = '88339';
SALARY_START SALARY_END
14-NOV-1967 08:30:00.00 25-NOV-1988 16:30:00.00
1 row selected
SQL> --
SQL> -- The SET DATE FORMAT DATE statement customizes the
SQL> -- output of the date format.
SQL> --
SQL> -- The output will appear in the form
SQL> -- 14 NOV 67, as specified by the date-number argument 6.
SQL> --
SQL> SET DATE FORMAT DATE 6;
SQL> SELECT SALARY_START, SALARY_END FROM SALARY_HISTORY-
cont> WHERE EMPLOYEE_ID = '88339';
SALARY_START SALARY_END
14 NOV 67 25 NOV 88
1 row selected
SQL> --
SQL> -- The SET DATE FORMAT TIME statement customizes
SQL> -- the output of the time format. The output will appear
SQL> -- in the form 16 h 30 min 0 s, as specified by the
SQL> -- time-number argument 20.
SQL> --
SQL> SET DATE FORMAT TIME 20;
SQL> SELECT SALARY_START, SALARY_END FROM SALARY_HISTORY-
cont> WHERE EMPLOYEE_ID = '88339';
SALARY_START SALARY_END
8 h 30 min 0 s 16 h 30 min 0 s
1 row selected
SQL> --
SQL> -- Note that the previous date example has deleted
SQL> -- the time output, and the previous time example has
SQL> -- deleted the date output.
SQL> --
SQL> -- If you want the display to continue to show
SQL> -- BOTH date and time, you must specify
SQL> -- both arguments with the SET DATE statement.
SQL> --
SQL> SET DATE FORMAT DATE 6, TIME 20;
SQL> SELECT SALARY_START, SALARY_END FROM SALARY_HISTORY-
cont> WHERE EMPLOYEE_ID = '88339';
SALARY_START SALARY_END
14 NOV 67 8 h 30 min 0 s 25 NOV 88 16 h 30 min 0 s
1 row selected
SQL> --
SQL> -- The next example changes the digit separator to a period and
SQL> -- the radix point to a comma:
SQL> --
SQL> ALTER TABLE SALARY_HISTORY -
cont> ALTER SALARY_AMOUNT EDIT STRING 'ZZZ,ZZZ.ZZ';
SQL> --
SQL> SET RADIX POINT ','
SQL> SET DIGIT SEPARATOR '.'
SQL> SELECT SALARY_AMOUNT FROM SALARY_HISTORY;
SALARY_AMOUNT
26.291,00
51.712,00
26.291,00
50.000,00
.
.
.
SQL> --
SQL> -- This example shows how you can use the SET LANGUAGE
SQL> -- statement to change the output of dates to a particular
SQL> -- language. This example shows the default English first,
SQL> -- followed by French.
SQL> --
SQL> -- Note that the time format is still based on
SQL> -- the SET DATE FORMAT TIME statement
SQL> -- previously executed in this example.
SQL> --
SQL> SELECT SALARY_START FROM SALARY_HISTORY;
SALARY_START
5 JUL 80 0 h 0 min 0 s
14 JAN 83 0 h 0 min 0 s
2 MAR 81 0 h 0 min 0 s
21 SEP 81 0 h 0 min 0 s
3 NOV 81 0 h 0 min 0 s
1 JUL 82 0 h 0 min 0 s
27 JAN 81 0 h 0 min 0 s
1 JUL 75 0 h 0 min 0 s
29 DEC 78 0 h 0 min 0 s
2 FEB 80 0 h 0 min 0 s
8 APR 79 0 h 0 min 0 s
19 AUG 77 0 h 0 min 0 s
.
.
.
SQL> --
SQL> SET LANGUAGE FRENCH
SQL> SELECT SALARY_START FROM SALARY_HISTORY;
SALARY_START
5 jul 80 0 h 0 min 0 s
14 jan 83 0 h 0 min 0 s
2 mar 81 0 h 0 min 0 s
21 sep 81 0 h 0 min 0 s
3 nov 81 0 h 0 min 0 s
1 jul 82 0 h 0 min 0 s
27 jan 81 0 h 0 min 0 s
1 jul 75 0 h 0 min 0 s
29 déc 78 0 h 0 min 0 s
2 fév 80 0 h 0 min 0 s
8 avr 79 0 h 0 min 0 s
19 aoû 77 0 h 0 min 0 s
.
.
.
SQL> --
Example 4: Using the SET statement to receive messages about
syntax that contains extensions to the ANSI/ISO SQL or MIA
standards
This example shows the output when flagging is turned on, first
for SQL92_ENTRY and then for MIA.
SQL> -- Flagging is off by default. When you enter a statement that
SQL> -- uses the data type VARCHAR, SQL does not issue a message.
SQL> --
SQL> SHOW FLAGGER MODE;
The flagger mode is OFF
SQL> CREATE TABLE TEST1 (TEXT_COL VARCHAR (100));
SQL> --
SQL> -- When you set the flagger to SQL92_ENTRY, SQL generates an
SQL> -- error message because VARCHAR is an extension to the standard.
SQL> --
SQL> SET FLAGGER SQL92_ENTRY ON
SQL> CREATE TABLE TEST2 (TEXT_COL VARCHAR (100));
%SQL-I-NONSTADTP, Nonstandard data type
SQL> --
SQL> -- With the flagger set to SQL92_ENTRY, SQL does not generate an
SQL> -- error message for the data type CHAR because it is an ANSI/ISO
SQL> -- standard data type.
SQL> --
SQL> CREATE TABLE TEST3 (TEXT_COL CHAR);
SQL> --
SQL> -- However, when you set the flagger to MIA, SQL generates two
SQL> -- error messages because data definition is not part of the MIA
SQL> -- standard. The first error message is caused by the CREATE
SQL> -- keyword; the second is caused by trying to create a table.
SQL> --
SQL> -- (Note that the SET FLAGGER statement itself is nonstandard.)
SQL> --
SQL> SET FLAGGER MIA ON
%SQL-I-NONSTASYN, Nonstandard syntax
SQL> CREATE TABLE TEST3 (TEXT_COL CHAR);
%SQL-I-NONSTASYN, Nonstandard syntax
%SQL-I-NONSTASYN, Nonstandard syntax
SQL>
Example 5: Using the SET statement to check for obsolete syntax
This example shows the output from an obsolete SQL statement when
the user specifies WARNING DEPRECATE, and the output from the
same statement when the user specifies WARNING NODEPRECATE.
SQL> --
SQL> -- By default, SQL sends warning messages when you use obsolete syntax.
SQL> --
SQL> DECLARE SCHEMA FILENAME personnel;
%SQL-I-DEPR_FEATURE, Deprecated Feature: SCHEMA (meaning ALIAS)
SQL> DISCONNECT ALL;
SQL> --
SQL> -- When you specify SET WARNING NODEPRECATE, SQL does not display warning
SQL> -- messages.
SQL> --
SQL> SET WARNING NODEPRECATE;
SQL> DECLARE SCHEMA FILENAME personnel;
SQL> DISCONNECT ALL;
Example 6: Setting page length
The following example uses the SET PAGE LENGTH command to change
the pagination length of HELP.
SQL> set page length 40;
SQL> show display
Output of the query header is enabled
Output of the row counter is enabled
Output using edit strings is enabled
Page length is set to 40 lines
Line length is set to 80 bytes
Display NULL values using "NULL"
Example 7: Saving the output from a script
The following example shows the use of SET LOGFILE to save the
output from a script without echoing the results.
1. The script being executed.
set verify;
start transaction read only;
set logfile (noecho) 'saved_date.log';
select rdb$flags from rdb$database;
set nologfile;
show alias;
rollback;
2. The output as seen during the Interactive SQL session.
SQL> start transaction read only;
SQL>
SQL> set logfile (noecho) 'saved_date.log';
SQL>
SQL> show alias;
Default alias:
Oracle Rdb database in file SQL$DATABASE
SQL> rollback;
3. The output saved in the log file.
SQL>
SQL> select rdb$flags from rdb$database;
RDB$FLAGS
0
1 row selected
SQL>
SQL> set nologfile;