2.9 Database Compatibility Issues for SQL Server

SQL Server and Oracle databases function differently in some areas, causing compatibility problems.

The compatibility issues are described in these topics.

• Implicit Transactions (Chained Mode)
  The gateway supports the ANSI-standard implicit transactions.
• Column Definitions
  By default, a SQL Server table column cannot contain null values unless NULL is specified in the column definition.
• Naming Rules
  These topics describe naming rule issues.
• Data Types
  These topics describe data type issues.
• Queries
  These topics describe query issues.
• Locking
  The locking model for an SQL Server database differs significantly from the Oracle model.

2.9.1 Implicit Transactions (Chained Mode)

The gateway supports the ANSI-standard implicit transactions.

SQL Server stored procedures must be written for this mode. Running implicit transactions allows the gateway to extend the Oracle two-phase commit protection to transactions updating Oracle and SQL Server databases.

2.9.2 Column Definitions

By default, a SQL Server table column cannot contain null values unless NULL is specified in the column definition.

SQL Server assumes all columns cannot contain null values unless you set a SQL Server option to override this default.

For an Oracle table, null values are allowed in a column unless NOT NULL is specified in the column definition.

2.9.3 Naming Rules

These topics describe naming rule issues.

• Rules for Naming Objects
  Oracle and SQL Server use different database object naming rules.
• Case Sensitivity
  The Oracle database defaults to uppercase unless you surround identifiers with double quote characters.

2.9.3.1 Rules for Naming Objects

Oracle and SQL Server use different database object naming rules.

For example, the maximum number of characters allowed for each object name can be different. Also, the use of single and double quotation marks, case sensitivity, and the use of alphanumeric characters can all be different.

See Also:

Oracle Database Reference and SQL Server documentation.

2.9.3.2 Case Sensitivity

The Oracle database defaults to uppercase unless you surround identifiers with double quote characters.

For example, to refer to the SQL Server table called emp, enter the name with double quote characters, as follows:

SQL> SELECT * FROM "emp"@MSQL;

However, to refer to the SQL Server table called emp owned by Scott from an Oracle application, enter the following:

SQL> SELECT * FROM "Scott"."emp"@MSQL;

If the SQL Server table called emp is owned by SCOTT, a table owner name in uppercase letters, you can enter the owner name without double quote characters, as follows:

SQL> SELECT * FROM SCOTT."emp"@MSQL;

or

SQL> SELECT * FROM scott."emp"@MSQL;

Oracle recommends that you surround all SQL Server object names with double quote characters and use the exact letter case for the object names as they appear in the SQL Server data dictionary. This convention is not required when referring to the supported Oracle data dictionary tables or views listed in Data Dictionary.

If existing applications cannot be changed according to these conventions, create views in Oracle to associate SQL Server names to the correct letter case. For example, to refer to the SQL Server table emp from an existing Oracle application by using only uppercase names, define the following view:

SQL> CREATE VIEW EMP (EMPNO, ENAME, SAL, HIREDATE)
      AS SELECT "empno", "ename", "sal", "hiredate"
      FROM "emp"@MSQL;

With this view, the application can issue statements such as the following:

SQL> SELECT EMPNO, ENAME FROM EMP;

Using views is a workaround solution that duplicates data dictionary information originating in the SQL Server data dictionary. You must be prepared to update the Oracle view definitions whenever the data definitions for the corresponding tables are changed in the SQL Server database.

2.9.4 Data Types

These topics describe data type issues.

• Binary Literal Notation
  Oracle SQL uses hexadecimal digits surrounded by single quotes to express literal values being compared or inserted into columns defined as data type RAW.
• Bind Variables With LONG Columns
  The gateway does not support using bind variables to update columns of data type LONG.
• Data Type Conversion
  SQL Server does not support implicit date conversions. Such conversions must be explicit.

2.9.4.1 Binary Literal Notation

Oracle SQL uses hexadecimal digits surrounded by single quotes to express literal values being compared or inserted into columns defined as data type RAW.

This notation is not converted to syntax compatible with the SQL Server VARBINARY and BINARY data types (a 0x followed by hexadecimal digits, surrounded by single quotes).

For example, the following statement is not supported:

SQL> INSERT INTO BINARY_TAB@MSQL VALUES ('0xff')

Where BINARY_TAB contains a column of data type VARBINARY or BINARY. Use bind variables when inserting into or updating VARBINARY and BINARY data types.

2.9.4.2 Bind Variables With LONG Columns

The gateway does not support using bind variables to update columns of data type LONG.

2.9.4.3 Data Type Conversion

SQL Server does not support implicit date conversions. Such conversions must be explicit.

For example, the gateway issues an error for the following SELECT statement:

SELECT DATE_COL FROM TEST@MSQL WHERE DATE_COL = "1-JAN-2004";

To avoid problems with implicit conversions, add explicit conversions, as in the following:

SELECT DATE_COL FROM TEST@MSQL WHERE DATE_COL = TO_DATE("1-JAN-2004")

See Also:

Data Type Conversion for more information about restrictions on data types.

2.9.5 Queries

These topics describe query issues.

• Row Selection
  SQL Server evaluates a query condition for all selected rows before returning any of the rows.
• Empty Strings
  Oracle processes an empty string in a SQL statement as a null value. SQL Server processes an empty string as an empty string.
• Empty Bind Variables
  For VARCHAR bind variables, the gateway passes empty bind variables to the SQL Server database as a NULL value.

2.9.5.1 Row Selection

SQL Server evaluates a query condition for all selected rows before returning any of the rows.

If there is an error in the evaluation process for one or more rows, no rows are returned even though the remaining rows satisfy the condition.

Oracle evaluates the query condition row-by-row and returns a row when the evaluation is successful. Rows are returned until a row fails the evaluation.

2.9.5.2 Empty Strings

Oracle processes an empty string in a SQL statement as a null value. SQL Server processes an empty string as an empty string.

When comparing an empty string the gateway passes literal empty strings to the SQL Server database without any conversion. If you intended an empty string to represent a null value, SQL Server does not process the statement that way; it uses the empty string.

You can avoid this problem by using NULL or IS NULL in the SQL statement instead of the empty string syntax, as in the following example:

SELECT * from "emp"@MSQL where "ename" IS NULL;

To select an empty string:

• For VARCHAR columns, the gateway returns an empty string to the Oracle database as NULL value.
• For CHAR columns, the gateway returns the full size of the column with each character as empty space (' ').

2.9.5.3 Empty Bind Variables

For VARCHAR bind variables, the gateway passes empty bind variables to the SQL Server database as a NULL value.

2.9.6 Locking

The locking model for an SQL Server database differs significantly from the Oracle model.

The gateway depends on the underlying SQL Server behavior, so the following possible scenarios can affect Oracle applications that access SQL Server through the gateway:

• Read access might block write access
• Write access might block read access
• Statement-level read consistency is not guaranteed

  See Also:

  SQL Server documentation for information about the SQL Server locking model.