Compatibility features for Oracle

Big SQL provides features that enable applications that were written for an Oracle database to use Big SQL with only minor modifications.

Features that are supported by default

Several Oracle compatibility features are always active. For information about these features, see Compatibility features that are always active.

The following features are also enabled by default in Big SQL.
Attention: Setting of the DB2_COMPATIBILITY_VECTOR registry variable is not required or recommended to enable Oracle compatibility features in Big SQL.
  • Queries can use the outer join operator (+) as alternate syntax within predicates of the WHERE clause.
  • A hierarchical query is a form of recursive query that retrieves a hierarchy, such as a bill of materials, from relational data by using a CONNECT BY clause.
  • The ROWNUM pseudocolumn returns a number that indicates the order in which the returned row in a query result set was selected.
  • The DUAL table DUAL table is a special one-row default table, similar to the SYSIBM.SYSDUMMY1 table.
  • Changed syntax for the TRUNCATE statement causes a truncate operation to be processed immediately, regardless of whether the IMMEDIATE keyword is specified.
  • Oracle data dictionary-compatible views are created automatically when you create a database.

Features that are supported when SQL_COMPAT='ORA'

In addition to the Oracle compatibility features that are now generally available in Big SQL, you can use the SQL_COMPAT global variable to activate the following custom Oracle compatibility features:
  • TRANSLATE scalar function syntax

    The syntax and semantics of the TRANSLATE scalar function in ORA compatibility mode are different than those in Big SQL. The from-string-exp is the second argument, and the to-string-exp is the third argument. If to-string-exp is shorter than from-string-exp, the extra characters in from-string-exp that are found in char-string-exp (the first argument) are removed; that is, the default pad-char-exp argument is effectively an empty string, unless a different pad character is specified in the fourth argument. For more information, see TRANSLATE scalar function.

  • SQL data-access-level enforcement
    Attention: Setting of the DB2_COMPATIBILITY_VECTOR registry variable is not recommended in Big SQL. Custom compatibility features should be enabled only by using the SQL_COMPAT global variable.
  • Support for compiling and executing PL/SQL statements and other language elements
    Attention: Setting of the DB2_COMPATIBILITY_VECTOR registry variable is not recommended in Big SQL. Custom compatibility features should be enabled only by using the SQL_COMPAT global variable.
  • Oracle database link syntax
    Attention: Setting of the DB2_COMPATIBILITY_VECTOR registry variable is not recommended in Big SQL. Custom compatibility features should be enabled only by using the SQL_COMPAT global variable.
  • Synonym usage
    Attention: Setting of the DB2_COMPATIBILITY_VECTOR registry variable is not recommended in Big SQL. Custom compatibility features should be enabled only by using the SQL_COMPAT global variable.