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 unlessNULL
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.
Parent topic: Sybase Gateway Features and Restrictions
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.
Parent topic: Database Compatibility Issues for Sybase
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.
Parent topic: Database Compatibility Issues for Sybase
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.
Parent topic: Database Compatibility Issues for Sybase
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.Parent topic: Naming Rules
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.
Parent topic: Naming Rules
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 typeRAW
. - Data Type Conversion
Sybase does not support implicit date conversions. Such conversions must be explicit.
Parent topic: Database Compatibility Issues for Sybase
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.
Parent topic: 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.Parent topic: 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
ForVARCHAR
bind variables, the gateway passes empty bind variables to the Sybase database as aNULL
value.
Parent topic: Database Compatibility Issues for Sybase
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.
Parent topic: Queries
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 (' ').
Parent topic: Queries
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.
Parent topic: Queries
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.
Parent topic: Database Compatibility Issues for Sybase
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.
Parent topic: Database Compatibility Issues for Sybase