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
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 – 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
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
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.
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.
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.
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.
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