2 Oracle Database Gateway for ODBC Features and Restrictions

After the gateway is installed and configured, you can use the gateway to access data in non-Oracle systems, pass native commands from applications to the non-Oracle system, perform distributed queries, and copy data.

This chapter contains the following sections:

Using the Pass-Through Feature
AUTO COMMIT
Known Restrictions
Known Problems

2.1 Using the Pass-Through Feature

The gateway can pass native commands or statements from the application to the non-Oracle system using the DBMS_HS_PASSTHROUGH package.

Use the DBMS_HS_PASSTHROUGH package in a PL/SQL block to specify the statement to be passed to the non-Oracle system, as follows:

DECLARE
    num_rows INTEGER;
BEGIN
    num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@SYBS('command');
END;
/

Where command cannot be one of the following:

BEGIN TRANSACTION
COMMIT
ROLLBACK
SAVE
SHUTDOWN

The DBMS_HS_PASSTHROUGH package supports passing bind values and executing SELECT statements.

Note:

TRUNCATE cannot be used in a pass-through statement.

Note:

As a general rule it is recommended that you COMMIT after each DDL statement in the pass-through especially when going to a Sybase database.

2.2 AUTO COMMIT

Some non-Oracle databases operate without logging. If read-only capability is desired under such environment, you need to set HS_TRANSACTION_MODEL=READ_ONLY_AUTOCOMMIT gateway parameter.

However, if you still need to have update capability, then set HS_TRANSACTION_MODEL=SINGLE_SITE_AUTOCOMMIT in the gateway initialization parameter file. Any update is committed immediately. Commit-confirm is not allowed for the targets operating without logging.

2.3 Known Restrictions

If you encounter incompatibility problems not listed in this section or in "Known Problems", contact Oracle Support Services. The following section describes the known restrictions:

BLOB and CLOB data cannot be read by pass-through queries
Updates or deletes that include unsupported functions within a WHERE clause are not allowed
Does not support stored procedures
Cannot participate in distributed transactions; they support single-site transactions only
Does not support multithreaded agents
Does not support updating LONG columns with bind variables
Does not support rowids
COMMIT or ROLLBACK in PL/SQL Cursor Loops Closes Open Cursors
SQL Syntax

2.3.1 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 the following error:

ORA-1002:  fetch out of sequence

To prevent this error, move the COMMIT or ROLLBACK statement outside the cursor loop.

2.3.2 SQL Syntax

This section lists restrictions on the following SQL syntax:

WHERE CURRENT OF Clause
CONNECT BY Clause
ROWID
EXPLAIN PLAN Statement

2.3.2.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.3.2.2 CONNECT BY Clause

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

2.3.2.3 ROWID

The Oracle ROWID implementation is not supported.

2.3.2.4 EXPLAIN PLAN Statement

The EXPLAIN PLAN statement is not supported.

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;
```
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.

2.3.3 Does not support CALLBACK links

Oracle Database Gateway for ODBC does not support CALLBACK links. Trying a CALLBACK link with the gateway will return the following error message:

ORA-02025: All tables in the SQL statement must be at the remote database

2.4 Known Problems

This section describes known problems and includes suggestions for correcting them when possible. If you have any questions or concerns about the problems, contact Oracle Support Services.

The following known problems are described in this section:

Encrypted Format Login
Date Arithmetic

2.4.1 Encrypted Format Login

Oracle database no longer supports the initialization parameter DBLINK_ENCRYPT_LOGIN. Up to version 7.3, this parameter's default TRUE value prevented the password for the login user ID from being sent over the network (in the clear). Later versions automatically encrypt the password.

2.4.2 Date Arithmetic

The following SQL expressions do not function correctly with the gateway:

date + number
number + date
date - number
date1 - date2

Statements with the preceding expressions are sent to the non-Oracle system without any translation. If the non-Oracle system does not support these date arithmetic functions, then the statements return an error.