1. User's Guide
  2. SQL Server Gateway Features and Restriction
  3. Known Restrictions

2.10 Known Restrictions

These topics describe known restrictions in Oracle Database Gateway for SQL Server 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.

Note:

If you have any questions or concerns about the restrictions, contact Oracle Support Services.
  • Multiple Open Statements
    Accessing SQL Server has the limitation that one open statement or cursor is allowed for each connection. If a second statement or cursor needs to open in the same transaction to access SQL Server, it requires a new connection.
  • Transactional Integrity
    The gateway cannot guarantee transactional integrity.
  • 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.
  • Stored Procedures
    The Oracle transaction manager or Oracle COMMIT or ROLLBACK commands cannot control changes issued through stored procedures that embed commits or rollbacks.
  • Pass-Through Feature
    If the SQL statements being passed through the gateway result in an implicit commit at the SQL Server database, the Oracle transaction manager is unaware of the commit and an Oracle ROLLBACK command cannot be used to roll back the transaction.
  • DDL Statements
    SQL Server requires some DDL statements to be executed in their own transaction, and only one DDL statement can be executed in a given transaction.
  • SQL Syntax
    These topics list restrictions on SQL syntax.
  • Functions
    This topic describes restrictions for using functions with Oracle Database Gateway for SQL Server.
  • SQL*Plus COPY Command with Lowercase Table Names
    When using the SQL*Plus COPY command with lowercase table names, wrap the table names in double quotation marks.
  • Database Links
    The gateway is not multithreaded and cannot support shared database links.

Parent topic: SQL Server Gateway Features and Restriction

2.10.1 Multiple Open Statements

Accessing SQL Server has the limitation that one open statement or cursor is allowed for each connection. If a second statement or cursor needs to open in the same transaction to access SQL Server, it requires a new connection.

Because of this limitation multiple open statements or cursors within the same transaction can lock each other because they use different connections to SQL Server.

To avoid this restriction, issue a commit, or modify the logic, or both.

Parent topic: Known Restrictions

2.10.2 Transactional Integrity

The gateway cannot guarantee transactional integrity.

For example, 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:

If you know that executing a particular statement causes an implicit commit in the target database, Oracle strongly recommends that your ensure that the statement is executed in its own transaction.

The gateway sets Autocommit Mode to Off when a connection is established to the SQL Server database.

Parent topic: Known Restrictions

2.10.3 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.

Parent topic: Known Restrictions

2.10.4 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.

This can result in the following error:

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.10.5 Stored Procedures

The Oracle transaction manager or Oracle COMMIT or ROLLBACK commands cannot control changes issued through stored procedures that embed commits or rollbacks.

When accessing stored procedures with result sets through the Oracle Database Gateway for SQL Server, you must work in the sequential mode of Heterogeneous Services.

When accessing stored procedures with multiple result sets through the Oracle Database Gateway for SQL Server, you must read all the result sets before continuing.

Output parameters of stored procedures must be initialized to an empty string.

Parent topic: Known Restrictions

2.10.6 Pass-Through Feature

If the SQL statements being passed through the gateway result in an implicit commit at the SQL Server 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.10.7 DDL Statements

SQL Server requires some DDL statements to be executed in their own transaction, and only one DDL statement can be executed in a given transaction.

If you use these DDL statements in a SQL Server stored procedure and you execute the stored procedure through the gateway using the procedural feature, or, if you execute the DDL statements through the gateway using the pass-through feature, an error condition might result. This is because the procedural feature and the pass-through feature of the gateway cannot guarantee that the DDL statements are executed in their own separate transaction.

The following SQL Server DDL statements can cause an error condition if you attempt to pass them with the gateway pass-through feature, or if you execute a SQL Server stored procedure that contains them:

  • ALTER DATABASE
  • CREATE DATABASE
  • CREATE INDEX
  • CREATE PROCEDURE
  • CREATE TABLE
  • CREATE VIEW
  • DISK INIT
  • DROP <object>
  • DUMP TRANSACTION
  • GRANT
  • LOAD DATABASE
  • LOAD TRANSACTION
  • RECONFIGURE
  • REVOKE
  • SELECT INTO
  • TRUNCATE TABLE
  • UPDATE STATISTICS

See Also:

SQL Server documentation for more information about DDL statements.

Parent topic: Known Restrictions

2.10.8 SQL Syntax

These topics list 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.
  • Functions in Subqueries
    Bind variables and expressions are not supported as operands in string functions or mathematical functions, when part of subquery in an INSERT, UPDATE, or DELETE SQL statement.
  • Parameters in Subqueries
    Due to a limitation in SQL Server, you cannot use parameters in subqueries.
  • Data Dictionary Table and Views in UPDATE Statement
    Data dictionary tables and views in the SET clause of an UPDATE statement are not supported.
  • ROWID
    The Oracle ROWID implementation is not supported.
  • TO_DATE
    TO_DATE is a reserved word and cannot be used as a database identifier name.
  • 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.

Parent topic: Known Restrictions

2.10.8.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.10.8.2 CONNECT BY Clause

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

Parent topic: SQL Syntax

2.10.8.3 Functions in Subqueries

Bind variables and expressions are not supported as operands in string functions or mathematical functions, when part of subquery in an INSERT, UPDATE, or DELETE SQL statement.

Parent topic: SQL Syntax

2.10.8.4 Parameters in Subqueries

Due to a limitation in SQL Server, you cannot use parameters in subqueries.

Parent topic: SQL Syntax

2.10.8.5 Data Dictionary Table and Views in UPDATE Statement

Data dictionary tables and views in the SET clause of an UPDATE statement are not supported.

Parent topic: SQL Syntax

2.10.8.6 ROWID

The Oracle ROWID implementation is not supported.

Parent topic: SQL Syntax

2.10.8.7 TO_DATE

TO_DATE is a reserved word and cannot be used as a database identifier name.

Parent topic: SQL Syntax

2.10.8.8 EXPLAIN PLAN Statement

The EXPLAIN PLAN statement is not supported.

Parent topic: SQL Syntax

2.10.9 Functions

This topic describes restrictions for using functions with Oracle Database Gateway for SQL Server.

The following restrictions apply to using functions:

  • Unsupported functions cannot be used in statements that refer to LONG columns.
  • 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 SQL Server.

Parent topic: Known Restrictions

2.10.10 SQL*Plus COPY Command with Lowercase Table Names

When using the SQL*Plus COPY command with lowercase table names, wrap the table names in double quotation marks.

For example:

copy from tkhouser/tkhouser@inst1 insert loc_tkhodept using select * from "tkhodept"@holink2;

Parent topic: Known Restrictions

2.10.11 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