2.9 Known Restrictions
The following sections describe the known restrictions and includes suggestions for dealing with them when possible.
If you encounter incompatibility problems not listed in this section or in "Known Problems", contact Oracle Support Services.
The following restriction also applies:
- When negative numbers are used as the second parameter in a
SUBSTR
function, incorrect results are returned. This is due to incompatibility between the OracleSUBSTR
function and the equivalent in Sybase.Note:
If you have any questions or concerns about the restrictions, contact Oracle Support Services.
- Transactional Integrity
The gateway cannot guarantee transactional integrity in the following cases. - Transaction Capability
The gateway does not support savepoints. - COMMIT or ROLLBACK in PL/SQL Cursor Loops Closes Open Cursors
AnyCOMMIT
orROLLBACK
issued in a PL/SQL cursor loop closes all open cursors, which can result in an error. - Stored Procedures
Oracle Database Gateway for Sybase and stored procedures. - Pass-Through Feature
DDL statements executed by Sybase using the gateway pass-through feature might fail if they are in a multi-statement transaction. - Sybase NCHAR and NVARCHAR Data Types
The gateway cannot select a column defined with a SybaseNCHAR
orNVARCHAR
data type. - SQL Syntax
The following topics discuss the restrictions on SQL syntax. - SQL*Plus COPY Command with Lowercase Table Names
You need to use double quotes to wrap around lowercase table names. - Database Links
The gateway is not multithreaded and cannot support shared database links.
Parent topic: Sybase Gateway Features and Restrictions
2.9.1 Transactional Integrity
The gateway cannot guarantee transactional integrity in the following cases.
-
When a statement that is processed by the gateway causes an implicit commit in the target database
-
When the target database is configured to work in autocommit mode
Note:
Oracle strongly recommends the following:
- If you know that executing a particular statement causes an implicit commit in the target database, then ensure that this statement is executed in its own transaction.
- Do not configure the target database to work in autocommit mode.
Parent topic: Known Restrictions
2.9.2 Transaction Capability
The gateway does not support savepoints.
If a distributed update transaction is under way involving the gateway and a user attempts to create a savepoint, the following error occurs:
ORA-02070: database dblink does not support savepoint in this context
By default, the gateway is configured as COMMIT_CONFIRM
and it is always the commit point site when the Sybase database is updated by the transaction.
Parent topic: Known Restrictions
2.9.3 COMMIT or ROLLBACK in PL/SQL Cursor Loops Closes Open Cursors
Any COMMIT
or ROLLBACK
issued in a PL/SQL cursor loop closes all open cursors, which can result in an error.
For example:
ORA-1002: fetch out of sequence
To prevent this error, move the COMMIT
or ROLLBACK
statement outside the cursor loop.
Parent topic: Known Restrictions
2.9.4 Stored Procedures
Oracle Database Gateway for Sybase and stored procedures.
Consider the following with regard to stored procedures:
- Changes issued through stored procedures that embed commits or rollbacks cannot be controlled by the Oracle transaction manager or Oracle
COMMIT
orROLLBACK
commands. - When accessing stored procedures with result sets through the Oracle Database Gateway for Sybase, you must work in the sequential mode of Heterogeneous Services.
- When accessing stored procedures with multiple result sets through the Oracle Database Gateway for Sybase, you must read all the result sets before continuing.
- Output parameters of stored procedures must be initialized to a
NULL
value. - Oracle Database Gateway for Sybase does not support output parameters or stored procedures with output parameters, inside a pass through query.
Parent topic: Known Restrictions
2.9.5 Pass-Through Feature
DDL statements executed by Sybase using the gateway pass-through feature might fail if they are in a multi-statement transaction.
Set the Sybase option "ddl in tran" to allow DDL statements in a transaction.
Oracle recommends that you place a DDL statement in its own transaction when executing such a statement with the pass-through feature. An explicit COMMIT
must be issued after the DDL statement.
If the SQL statements being passed through the gateway result in an implicit commit at the Sybase database, the Oracle transaction manager is unaware of the commit and an Oracle ROLLBACK
command cannot be used to roll back the transaction.
Parent topic: Known Restrictions
2.9.6 Sybase NCHAR and NVARCHAR Data Types
The gateway cannot select a column defined with a Sybase NCHAR
or NVARCHAR
data type.
Parent topic: Known Restrictions
2.9.7 SQL Syntax
The following topics discuss the restrictions on SQL syntax.
- WHERE CURRENT OF Clause
UPDATE
andDELETE
statements with theWHERE CURRENT OF
clause are not supported by the gateway because they rely on the OracleROWID
implementation. - CONNECT BY Clause
The gateway does not support theCONNECT BY
clause in aSELECT
statement. - ROWID
The OracleROWID
implementation is not supported. - Subqueries in INSERT Statement
Subqueries ofINSERT
statements cannot use multiple aliases for the same table. - EXPLAIN PLAN Statement
TheEXPLAIN PLAN
statement is not supported.
See Also:
Supported SQL Syntax and Functions for more information about restrictions on SQL syntax.Parent topic: Known Restrictions
2.9.7.1 WHERE CURRENT OF Clause
UPDATE
and DELETE
statements with the WHERE CURRENT OF
clause are not supported by the gateway because they rely on the Oracle ROWID
implementation.
To update or delete a specific row through the gateway, a condition style WHERE
clause must be used.
Parent topic: SQL Syntax
2.9.7.2 CONNECT BY Clause
The gateway does not support the CONNECT BY
clause in a SELECT
statement.
Parent topic: SQL Syntax
2.9.7.4 Subqueries in INSERT Statement
Subqueries of INSERT
statements cannot use multiple aliases for the same table.
For example, the following statement is not supported:
SQL> INSERT INTO "emp_target"@SYBS SELECT a."empno" FROM "emp_source"@SYBS a, "emp_source"@SYBS b WHERE b."empno"=9999
Parent topic: SQL Syntax
2.9.7.5 EXPLAIN PLAN Statement
The EXPLAIN PLAN
statement is not supported.
Parent topic: SQL Syntax
2.9.8 SQL*Plus COPY Command with Lowercase Table Names
You need to use double quotes to wrap around lowercase table names.
For example:
copy from tkhouser/tkhouser@inst1 insert loc_tkhodept using select * from "tkhodept"@holink2;
Parent topic: Known Restrictions
2.9.9 Database Links
The gateway is not multithreaded and cannot support shared database links.
Each gateway session spawns a separate gateway process and connections cannot be shared.
Parent topic: Known Restrictions