Specifies whether or not SQL behavior in certain instances
complies with the ANSI/ISO SQL standard. The current default
behavior in these instances is noncompliant.
NOTE
SQL provides the following new statements to replace the SET
ANSI statement:
o SET DEFAULT DATE FORMAT replaces SET ANSI DATE; see the
SET DEFAULT DATE FORMAT statement.
o SET KEYWORD RULES replaces SET ANSI IDENTIFIERS; see the
SET_KEYWORD_RULES statement.
o SET QUOTING RULES replaces SET ANSI QUOTING; see the SET_
QUOTING_RULES statement.
o SET VIEW UPDATE RULES is new; see the SET_VIEW_UPDATE_
RULES statement.
In addition, SQL provides the SET DIALECT statement to
let you specify, with one statement, settings for all of
these statements. See the SET_DIALECT statement for more
information.
SQL does not return a deprecated feature message if you use
the SET ANSI statement.
1 – Environment
You can use the SET ANSI statement only in interactive SQL.
2 – Format
(B)0[m[1;4mSET[m[1m [1;4mANSI[m[1m qwq> [1;4mDATE[m[1m qqqqqqqqwqw> [1;4mON[m[1m qwq> [m [1m tq> [1;4mIDENTIFIERS[m[1m qu m> [1;4mOFF[m[1m j [m [1m mq> [1;4mQUOTING[m[1m qqqqqj [m
3 – Arguments
3.1 – DATE
ON | OFF
Specifies the default interpretation for columns with the DATE
data type, and the data type of the CURRENT_TIMESTAMP function.
The DATE and CURRENT_TIMESTAMP data types, can be either VMS
ADT or ANSI. By default, both data types are interpreted as DATE
VMS. The VMS format contains YEAR TO SECOND fields, just as a
TIMESTAMP does.
You can change DATE and CURRENT_TIMESTAMP to ANSI format with
the SET DEFAULT DATE FORMAT statement, the precompiler DEFAULT
DATE FORMAT clause in a DECLARE MODULE statement embedded in a
program, or the module language DEFAULT DATE FORMAT clause in a
module file. The ANSI format DATE contains only the YEAR TO DAY
fields.
You must use the SET DEFAULT DATE FORMAT statement before
creating domains or tables. You cannot use this statement to
modify the data type once you have created a column or table.
3.2 – IDENTIFIERS
ON | OFF
Specifies whether or not SQL checks statements that use reserved
words as identifiers. If you specify SET ANSI IDENTIFIERS ON, SQL
checks statements for reserved words from the ANSI/ISO standard.
You must enclose reserved words in double quotation marks to
supply them as identifiers in SQL statements. If you do not,
SQL issues an informational message after such statements when
you enable reserved-word checking. For a list of the reserved
words deprecated as identifiers, see the Oracle Rdb SQL Reference
Manual.
When you specify SET ANSI IDENTIFIERS OFF, SQL does not check
identifiers. By default, SQL does not check identifiers.
3.3 – QUOTING
ON | OFF
Allows you to use double quotation marks to delimit the alias
and catalog name pair in subsequent statements. By default,
SQL syntax allows only single quotation marks. To comply with
ANSI/ISO SQL standard naming conventions, ANSI QUOTING must be
on. You must set ANSI QUOTING on to use multischema database
naming.
4 – Example
Example 1: Setting CURRENT_TIMESTAMP to ANSI format
In the following example, SQL issues an error message because
CURRENT_TIMESTAMP is an ADT data type by default, and TIMESTAMP
is an ANSI data type. The SET ANSI DATE ON statement changes the
default CURRENT_TIMESTAMP to ANSI format.
SQL> begin
cont> declare :logging_date timestamp;
cont> set :logging_date = current_timestamp;
cont> trace :logging_date;
cont> end;
%SQL-F-UNSDATASS, Unsupported date/time assignment from <Source> to LOGGING_DATE
SQL> SET ANSI DATE ON;
SQL> begin
cont> declare :logging_date timestamp;
cont> set :logging_date = current_timestamp;
cont> trace :logging_date;
cont> end;
Example 2: Using the SET ANSI IDENTIFIERS statement to check for
reserved words
This example shows the output from an SQL statement that creates
a domain and specifies the ANSI89 reserved word CONTINUE as the
user-supplied name for that domain. The SET ANSI IDENTIFIERS ON
statement requires that you use uppercase characters for the name
and enclose it in double quotation marks.
SQL> set ansi identifiers on;
SQL> create domain continue char(5);
%SQL-F-RES_WORD_AS_IDE, Keyword CONTINUE used as an identifier
SQL> create domain "CONTINUE" char(5);
SQL>