2.8 Database Compatibility Issues for Sybase

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

• Chained Mode
  The gateway supports the ANSI-standard chained mode.
• Column Definitions
  By default, a Sybase table column cannot contain null values unless NULL is specified in the column definition.
• Naming Rules
  The following topics discuss the naming rules issues.
• Data Types
  The following topics discuss data type issues.
• Queries
  The following topics discuss query issues.
• Locking
  The locking model for a Sybase database differs significantly from the Oracle model.
• Sybase Identifiers Length Limit
  By default, the gateway will always quote identifiers. However, certain Sybase releases have a limit of 30 characters for identifiers such as table or column names and quotes are considered part of the names when checking against this limit.

2.8.1 Chained Mode

The gateway supports the ANSI-standard chained mode.

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

2.8.2 Column Definitions

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

In compliance with the ANSI standard, the Sybase database option "allow nulls by default" can be set to true to change the default column definition to NULL.

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

2.8.3 Naming Rules

The following topics discuss the naming rules issues.

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

2.8.3.1 Rules for Naming Objects

Oracle and Sybase 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 Sybase documentation.

2.8.3.2 Case Sensitivity

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

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

SQL> SELECT * FROM "emp"@SYBS;

However, to refer to the Sybase table called emp owned by SCOTT from an Oracle application, enter the following:

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

If the Sybase 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"@SYBS;

Or:

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

Oracle recommends that you surround all Sybase object names with double quote characters and use the exact letter case for the object names as they appear in the Sybase 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 Sybase names to the correct letter case. For example, to refer to the Sybase 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"@SYBS;

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 Sybase data dictionary. You must be prepared to update the Oracle view definitions whenever the data definitions for the corresponding tables are changed in the Sybase database.

2.8.4 Data Types

The following topics discuss 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.
• Data Type Conversion
  Sybase does not support implicit date conversions. Such conversions must be explicit.

2.8.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 Sybase 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@SYBS 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.8.4.2 Data Type Conversion

Sybase 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@SYBS WHERE DATE_COL = "1-JAN-2001";

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

SELECT DATE_COL FROM TEST@SYBS WHERE DATE_COL = TO_DATE("1-JAN-2001")

See Also:

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

2.8.5 Queries

The following topics discuss query issues.

• Row Selection
  Sybase 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. Sybase processes an empty string as an empty string.
• Empty Bind Variables
  For VARCHAR bind variables, the gateway passes empty bind variables to the Sybase database as a NULL value.

2.8.5.1 Row Selection

Sybase 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.8.5.2 Empty Strings

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

When comparing an empty string, the gateway passes literal empty strings to the Sybase database without any conversion. If you intended an empty string to represent a null value, Sybase 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"@SYBS where "ename" IS NULL;

Selecting 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.8.5.3 Empty Bind Variables

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

2.8.6 Locking

The locking model for a Sybase database differs significantly from the Oracle model.

The gateway depends on the underlying Sybase behavior, so Oracle applications that access Sybase through the gateway can be affected by the following possible scenarios:

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

  See Also:

  Sybase documentation for information about the Sybase locking model.

2.8.7 Sybase Identifiers Length Limit

By default, the gateway will always quote identifiers. However, certain Sybase releases have a limit of 30 characters for identifiers such as table or column names and quotes are considered part of the names when checking against this limit.

Therefore, when quotes are used, you can only specify 28 characters. In order to support the maximum length limit in those Sybase releases, you need to specify HS_FDS_QUOTE_IDENTIFIER=FALSE in your gateway initialization parameter file. Setting this initialization parameter will cause the gateway to send identifiers without quotes. However, it has the side effect of precluding the use of identifiers that contain dots (.) or spaces, and the identifiers will follow the case sensitivity of the Sybase database being used.