Specifies the settings of the current connection for the
following characteristics:
o Whether the length of character string parameters, columns,
and domains are interpreted as characters or octets. This can
also be specified by using the SET CHARACTER LENGTH statement.
o Whether double quotation marks are interpreted as string
literals or delimited identifiers. This can also be specified
by using the SET QUOTING RULES statement.
o Whether or not identifiers can be keywords. This can also be
specified by using the SET KEYWORD RULES statement.
o Which views are read-only. This can also be specified by using
the SET VIEW UPDATE RULES statement.
o Whether columns with the DATE or CURRENT_TIMESTAMP data type
are interpreted as VMS or SQL99 format. This can also be
specified by using the SET DEFAULT DATE FORMAT statement.
o Whether character sets change. Character sets can be changed
using the SET DEFAULT CHARACTER SET, SET NATIONAL CHARACTER
SET, SET IDENTIFIER CHARACTER SET, and SET LITERAL CHARACTER
SET statements.
The SET DIALECT statement lets you specify several settings with
one command, instead of specifying each setting individually.
The following table shows the settings for each option.
Table 1-6 Dialect Settings
ORACLE
Characteristic SQL99 MIA SQLV40 Dialects
Character length Characters Characters Octets Characters
Quoting rules Delimited Delimited Literal Delimited
identi- identi- identifier
fier fier
Keywords allowed No No Yes Yes
as identifiers
View update ANSI/ISO ANSI/ISO Oracle ANSI/ISO SQL
rules SQL rules SQL rules Rdb rules rules
Default date DATE ANSI DATE ANSI DATE VMS DATE VMS
format
Default Not KATAKANA Not Not changed
character set changed changed
National Not KANJI Not Not changed
character set changed changed
Identifier Not DEC_KANJI Not Not changed
character set changed changed
Literal Not KATAKANA Not Not changed
character set changed changed
Default Not Deferrable Deferrable Not
evaluation for Deferrable Deferrable
constraints
Oracle Corporation recommends that you set the dialect to SQL99
or SQL92, unless you need to maintain compatibility with an
earlier dialect.
1 – Environment
You can use the SET DIALECT statement:
o In interactive SQL
o Embedded in host language programs to be precompiled to effect
the processing of dynamic SQL statements (use the DIALECT
clause to effect dialect changes in the precompiled source)
o As part of a procedure in an SQL module (but may not be in a
compound statement)
o In dynamic SQL as a statement to be dynamically executed
However, the ORACLE dialects can be used only in the interactive
SQL and dynamic SQL environments.
2 – Format
SET DIALECT ---> runtime-options ------->
runtime-options
--+---> 'string-literal' ------+----->
+---> parameter -------------+
+---> parameter-marker ------+
3 – Arguments
3.1 – ORACLE_LEVEL1
Specifies the following behavior:
o The same dialect rules as SQL92 are in effect minus reserved
word checking and the DATE ANSI format.
o The ORACLE LEVEL1 dialect allows the use of aliases to
reference (or link) to tables in data manipulation statements
like SELECT, DELETE, INSERT, and UPDATE. For example:
SQL> ATTACH 'ALIAS pers_alias FILENAME mf_personnel';
SQL> SET DIALECT 'ORACLE LEVEL1';
SQL> SELECT * FROM employees@pers_alias
cont> WHERE employee_id = '00164';
EMPLOYEE_ID LAST_NAME FIRST_NAME MIDDLE_INITIAL
ADDRESS_DATA_1 ADDRESS_DATA_2 CITY
STATE POSTAL_CODE SEX BIRTHDAY STATUS_CODE
00164 Toliver Alvin A
146 Parnell Place Chocorua
NH 03817 M 28-Mar-1947 1
1 row selected
Alias references are only allowed on the table name and not on
column names. You cannot put a space between the table name,
the at (@) sign, and the alias name.
If you specify a schema name when referencing an Oracle Rdb
database, the schema name is ignored unless the multischema
attribute is on.
o The following basic predicate for inequality comparisons is
supported:
!=
The != basic predicate requires that the ORACLE LEVEL1 dialect
be set to avoid confusion with the interactive SQL comment
character.
o When using dynamic SQL, the client application can specify a
synonym for the parameter marker (?). For example, :name, :1,
:2, and so on.
o The string concatenation operator and the CONCAT function
treat nulls as zero-length strings.
o The default date format is DATE VMS which is capable of doing
arithmetic in the ORACLE LEVEL1 dialect only. Addition and
subtraction can be done with numeric data types that are
implicitly cast to the INTERVAL DAY data type. Fractions are
rounded to the nearest whole integer.
o Zero length strings are null. When using an Oracle RDBMS
database, a VARCHAR of zero length is considered null. While
the Oracle Rdb ORACLE LEVEL1 dialect does not remove zero
length strings from the database, it does make them difficult
to create. The following rules are in effect:
- Empty literal strings (for example, '') are considered
literal nulls.
- Any function that encounters a zero length string returns
a null in its place. This includes stored and external
functions returning a VARCHAR data type regardless of the
dialect under which they were compiled. It also includes
the TRIM and SUBSTRING built-in functions.
- Parameters with the VARCHAR data type and a length of zero
are treated as null.
The best way to avoid zero length strings from being seen
by an Oracle RDBMS application is to only use views compiled
under the ORACLE dialects and to modify tables with VARCHAR
columns to remove zero length strings. The following example
shows how to remove zero length strings from a VARCHAR column
in a table:
SQL> UPDATE tab1 SET col1 = NULL WHERE CHARACTER_LENGTH(col1) = 0;
If modifying the table is not possible or if a view compiled
in another dialect containing VARCHAR functions must be used,
then create a new view under the ORACLE dialect referring to
that table or view to avoid the zero length VARCHAR string.
The following example shows how to avoid selecting zero length
strings from a VARCHAR column in a table or non-Oracle dialect
view:
SQL> SET DIALECT 'ORACLE LEVEL1';
SQL> CREATE VIEW view1 (col1, col2)
cont> AS SELECT SUBSTRING(col1 FROM 1 FOR 2000), col2 FROM tab1;
The Oracle Rdb optimizer is more efficient if data is selected
without the use of functions. Therefore, the previous example
is best used only if you suspect zero length strings have been
inserted into the table and it is necessary to avoid them.
o The ROWNUM keyword is allowed in select expressions and limits
the number of rows returned in the query. The following
example limits the number of rows returned by the SELECT
statement to 9 rows:
SQL> ATTACH 'FILENAME mf_personnel';
SQL> SET DIALECT 'ORACLE LEVEL1';
SQL> SELECT last_name FROM EMPLOYEES WHERE ROWNUM < 10;
LAST_NAME
Toliver
Smith
Dietrich
Kilpatrick
Nash
Gray
Wood
D'Amico
Peters
9 rows selected
Conditions testing for ROWNUM values greater than or equal to
a positive integer are always false and, therefore, return no
rows. For example:
SQL> SELECT last_name FROM EMPLOYEES WHERE ROWNUM > 10;
0 rows selected
SQL> SELECT last_name FROM EMPLOYEES WHERE ROWNUM = 10;
0 rows selected
3.2 – ORACLE_LEVEL2
This includes all the behavior describe for ORACLE LEVEL1 plus
the following changes:
o The same dialect rules as SQL99 are in effect minus reserved
word checking and the DATE ANSI format.
o Concatenate (||) and the CONCAT function allow for all data
types, not just character types (CHAR, and VARCHAR). The
numeric, or date/time values are converted to VARCHAR prior
to the concatenation.
o Date subtraction results in a floating result. Partial days
are now represented by a fraction portion.
o This is not an exhaustive list. Refer to Oracle Rdb Release
Notes for additional semantic changes for dialect ORACLE
LEVEL2.
3.3 – parameter
Specifies the value of the runtime-options, which must be one of
the following:
o SQL99
o SQL92
o SQL89
o MIA
o SQLV40
o ORACLE LEVEL1
3.4 – parameter-marker
Specifies the value of the runtime-options, which must be one of
the following:
o SQL99
o SQL92
o SQL89
o MIA
o SQLV40
o ORACLE LEVEL1
3.5 – SQL89_MIA
Specifies the following behavior:
o The length of character string parameters, columns, and
domains is interpreted as characters, rather than octets.
o Double quotation marks are interpreted as delimited
identifiers.
o Keywords cannot be used as identifiers unless they are
enclosed within double quotation marks.
o The ANSI/ISO SQL standard for updatable views is applied to
all views created during compilation. Views that do not comply
with the ANSI/ISO SQL standard for updatable views cannot be
updated.
The ANSI/ISO SQL standard for updatable views requires the
following conditions to be met in the SELECT statement:
- The DISTINCT keyword is not specified.
- Only column names can appear in the select list. Each
column name can appear only once. Functions and expressions
such as max(column_name) or column_name +1 cannot appear in
the select list.
- The FROM clause refers to only one table. This table must
be either a base table, global temporary table, local
temporary table, or a derived table that can be updated.
- The WHERE clause does not contain a subquery.
- The GROUP BY clause is not specified.
- The HAVING clause is not specified.
If you specify MIA, SQL sets the character sets as follows:
o Default character set: KATAKANA
o National character set: KANJI
o Identifier character set: DEC_KANJI
o Literal character set: KATAKANA
o The constraint evaluation time is DEFERRABLE INITIALLY
DEFERRED.
3.6 – SQL92
Specifies the following behavior:
o The length of character string parameters, columns, and
domains is interpreted as characters, rather than octets.
o Double quotation marks are interpreted as delimited
identifiers.
o Keywords cannot be used as identifiers unless they are
enclosed within double quotation marks.
o The ANSI/ISO SQL standard for updatable views is applied to
all views created during compilation. Views that do not comply
with the ANSI/ISO SQL standard for updatable views cannot be
updated.
The ANSI/ISO SQL standard for updatable views requires the
following conditions to be met in the SELECT statement:
- The DISTINCT keyword is not specified.
- Only column names can appear in the select list. Each
column name can appear only once. Functions and expressions
such as max(column_name) or column_name +1 cannot appear in
the select list.
- The FROM clause refers to only one table. This table must
be either a base table, global temporary table, local
temporary table, or a derived table that can be updated.
- The WHERE clause does not contain a subquery.
- The GROUP BY clause is not specified.
- The HAVING clause is not specified.
o The DATE and CURRENT_TIMESTAMP data types are interpreted as
SQL format. The SQL (ANSI) format DATE contains only the YEAR
TO DAY fields.
o Conversions between character data types when storing data
or retrieving data raise exceptions or warnings in certain
situations. For further explanation of these situations, see
the Data_Types HELP topic.
o You can specify DECIMAL or NUMERIC for formal parameters in
SQL modules and declare host language parameters with packed
decimal or signed numeric storage format. SQL generates
an error message if you attempt to exceed the precision
specified.
o The USER keyword specifies the current active user name for a
request.
o A warning is generated when a NULL value is eliminated from a
SET function.
o The WITH CHECK OPTION clause on views returns a discrete error
code from an integrity constraint failure.
o An exception is generated with terminated C strings that are
not NULL.
o The default on constraint evaluation time is set to NOT
DEFERRABLE INITIALLY IMMEDIATE.
3.7 – SQL99
Specifies that the SQL language conforms to SQL:1999 SQL Database
Language Standard.
This includes all the behavior describe for SQL92 plus the
following changes:
o The FOREIGN KEY constraint may list the column names in the
REFERENCES list in any order. In other dialects the column
names must be in the same order as the referenced PRIMARY KEY
or UNIQUE constraint.
o This is not an exhaustive list. Refer to the Oracle Rdb
Release Notes for additional semantic changes for dialect
SQL99.
3.8 – SQLV40
Specifies the following behavior:
o The length of character string parameters, columns, and
domains is interpreted as octets, rather than characters.
o Double quotation marks are interpreted as string literals.
o Keywords can be used as identifiers.
o The ANSI/ISO SQL standard for updatable views is not
applied. Instead, SQL considers views that meet the following
conditions to be updatable:
- The DISTINCT keyword is not specified.
- The FROM clause refers to only one table. This table must
be either a base table, global temporary table, local
temporary table, or a derived table that can be updated.
- The WHERE clause does not contain a subquery.
- The GROUP BY clause is not specified.
- The HAVING clause is not specified.
o The DATE and CURRENT_TIMESTAMP data types are interpreted as
VMS format. The VMS format DATE and CURRENT_TIMESTAMP contain
YEAR TO SECOND fields.
o The constraint evaluation time is DEFERRABLE INITIALLY
DEFERRED.
The default is SQLV40.
3.9 – 'string-literal'
Specifies the value of the runtime-options, which must be one of
the following:
o SQL99
o SQL92
o SQL89
o MIA
o SQLV40
o ORACLE LEVEL1
o ORACLE LEVEL2
4 – Examples
Example 1: Setting the characteristics to SQL92
SQL> ATTACH 'ALIAS MIA1 FILENAME MIA_CHAR_SET DISPLAY CHARACTER SET SHIFT_JIS';
SQL> CONNECT TO 'ALIAS MIA1 FILENAME MIA_CHAR_SET' AS 'TEST';
SQL> SHOW CONNECTIONS TEST;
Connection: TEST
Default alias is RDB$DBHANDLE
Default catalog name is RDB$CATALOG
Default schema name is SMITH
Dialect: SQLV40
Default character unit: OCTETS
Keyword Rules: SQLV40
View Rules: SQLV40
Default DATE type: DATE VMS
Quoting Rules: SQLV40
Optimization Level: DEFAULT
Hold Cursors default: WITH HOLD PRESERVE NONE
Quiet commit mode: OFF
Compound transactions mode: EXTERNAL
Default character set is DEC_MCS
National character set is DEC_MCS
Identifier character set is SHIFT_JIS
Literal character set is SHIFT_JIS
Display character set is SHIFT_JIS
Alias MIA1:
Identifier character set is DEC_KANJI
Default character set is KATAKANA
National character set is KANJI
SQL> --
SQL> -- Change the environment from SQLV40 to MIA. Notice that the session
SQL> -- character sets change.
SQL> --
SQL> SET DIALECT 'MIA';
SQL> SHOW CONNECTIONS TEST;
Connection: TEST
Default alias is RDB$DBHANDLE
Default catalog name is RDB$CATALOG
Default schema name is SMITH
Dialect: MIA
Default character unit: CHARACTERS
Keyword Rules: MIA
View Rules: ANSI/ISO
Default DATE type: DATE ANSI
Quoting Rules: ANSI/ISO
Optimization Level: DEFAULT
Hold Cursors default: WITH HOLD PRESERVE NONE
Quiet commit mode: OFF
Compound transactions mode: EXTERNAL
Default character set is KATAKANA
National character set is KANJI
Identifier character set is DEC_KANJI
Literal character set is KATAKANA
Display character set is SHIFT_JIS
Alias MIA1:
Identifier character set is DEC_KANJI
Default character set is KATAKANA
National character set is KANJI
SQL> --
SQL> -- Change the environment from MIA to SQL99. Notice that the
SQL> -- session characters DO NOT change from the MIA settings.
SQL> --
SQL> SET DIALECT 'SQL99';
SQL> SHOW CONNECTIONS TEST;
Connection: TEST
Default alias is RDB$DBHANDLE
Default catalog name is RDB$CATALOG
Default schema name is SMITH
Dialect: SQL99
Default character unit: CHARACTERS
Keyword Rules: SQL99
View Rules: ANSI/ISO
Default DATE type: DATE ANSI
Quoting Rules: ANSI/ISO
Optimization Level: DEFAULT
Hold Cursors default: WITH HOLD PRESERVE NONE
Quiet commit mode: ON
Compound transactions mode: EXTERNAL
Default character set is KATAKANA
National character set is KANJI
Identifier character set is DEC_KANJI
Literal character set is KATAKANA
Display character set is SHIFT_JIS
Alias MIA1:
Identifier character set is DEC_KANJI
Default character set is KATAKANA
National character set is KANJI
Example 2: Saving and restoring dialect in interactive SQL
This example shows the use of declared variables in interactive
SQL to save (using GET ENVIRONMENT) and restore the dialect
during execution of a script that requires an alternate
dialect. This example simply displays the dialect using the SHOW
CONNECTION statement.
SQL> set dialect 'sql99';
SQL> -- save current dialect
SQL> declare :dialect char(40);
SQL> get environment (session) :dialect = DIALECT;
SQL> print :dialect;
DIALECT
SQL99
SQL> set dialect 'oracle level2';
SQL> show connection rdb$default_connection;
Connection: RDB$DEFAULT_CONNECTION
Default alias is RDB$DBHANDLE
Default catalog name is RDB$CATALOG
Default schema name is SMITH
Dialect: SQL99 (ORACLE LEVEL2)
Default character unit: CHARACTERS
Keyword Rules: SQL99
View Rules: ANSI/ISO
Default DATE type: DATE VMS
Quoting Rules: ANSI/ISO
Optimization Level: DEFAULT
Hold Cursors default: WITH HOLD PRESERVE NONE
Quiet commit mode: ON
Compound transactions mode: EXTERNAL
Default character set is DEC_MCS
National character set is DEC_MCS
Identifier character set is DEC_MCS
Literal character set is DEC_MCS
Display character set is UNSPECIFIED
SQL>
SQL> -- restore previous dialect
SQL> set dialect :dialect;
SQL> show connection rdb$default_connection;
Connection: RDB$DEFAULT_CONNECTION
Default alias is RDB$DBHANDLE
Default catalog name is RDB$CATALOG
Default schema name is SMITH
Dialect: SQL99
Default character unit: CHARACTERS
Keyword Rules: SQL99
View Rules: ANSI/ISO
Default DATE type: DATE ANSI
Quoting Rules: ANSI/ISO
Optimization Level: DEFAULT
Hold Cursors default: WITH HOLD PRESERVE NONE
Quiet commit mode: ON
Compound transactions mode: EXTERNAL
Default character set is DEC_MCS
National character set is DEC_MCS
Identifier character set is DEC_MCS
Literal character set is DEC_MCS
Display character set is UNSPECIFIED
SQL>