1  SET_ANSI
   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.
 

2  Environment
   You can use the SET ANSI statement only in interactive SQL.
 

2  Format

 SET ANSI -+-> DATE --------+-+> ON -+->
           +-> IDENTIFIERS -+ +> OFF +
           +-> QUOTING -----+

 

2  Arguments
 

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

2  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>