1 SET_DIALECT 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. 2 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 ------+ 2 Arguments 3 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 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 3 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 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 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 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 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 '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 2 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>