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;