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 Oracle SUBSTR 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
  Any COMMIT or ROLLBACK 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 Sybase NCHAR or NVARCHAR 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.

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.

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.

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.

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 or ROLLBACK 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.

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.

2.9.6 Sybase NCHAR and NVARCHAR Data Types

The gateway cannot select a column defined with a Sybase NCHAR or NVARCHAR data type.

2.9.7 SQL Syntax

The following topics discuss the restrictions on SQL syntax.

• 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.
• CONNECT BY Clause
  The gateway does not support the CONNECT BY clause in a SELECT statement.
• ROWID
  The Oracle ROWID implementation is not supported.
• Subqueries in INSERT Statement
  Subqueries of INSERT statements cannot use multiple aliases for the same table.
• EXPLAIN PLAN Statement
  The EXPLAIN PLAN statement is not supported.

See Also:

Supported SQL Syntax and Functions for more information about restrictions on SQL syntax.

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.

2.9.7.2 CONNECT BY Clause

The gateway does not support the CONNECT BY clause in a SELECT statement.

2.9.7.3 ROWID

The Oracle ROWID implementation is not supported.

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

2.9.7.5 EXPLAIN PLAN Statement

The EXPLAIN PLAN statement is not supported.

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;

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.