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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

2.10.8.2 CONNECT BY Clause

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

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.

2.10.8.4 Parameters in Subqueries

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

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.

2.10.8.6 ROWID

The Oracle ROWID implementation is not supported.

2.10.8.7 TO_DATE

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

2.10.8.8 EXPLAIN PLAN Statement

The EXPLAIN PLAN statement is not supported.

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.

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;

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.