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.

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.

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.

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.

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.

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.

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.

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

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.

To update or delete a specific row through the gateway, a condition style WHERE clause must be used.

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.

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.

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;

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.