|Oracle® Database Gateway for ODBC User's Guide
11g Release 2 (11.2)
Part Number E12070-02
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:
The gateway can pass native commands or statements from the application to the non-Oracle system using 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:
DBMS_HS_PASSTHROUGH package supports passing bind values and executing
TRUNCATEcannot be used in a pass-through statement.
Note:As a general rule it is recommended that you
COMMITafter each DDL statement in the pass-through especially when going to a Sybase database.
See Also:Oracle Database PL/SQL Packages and Types Reference and Chapter 3 of Oracle Database Heterogeneous Connectivity User's Guide for more information about the
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.
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:
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
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
ROLLBACK statement outside the cursor loop.
This section lists restrictions on the following SQL syntax:
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.
EXPLAIN PLAN statement is not supported.
COPY Command with Lowercase Table Names
You need to use double quotes to wrap around lowercase table names.
copy from tkhouser/tkhouser@inst1 insert loc_tkhodept using select * from "tkhodept"@holink2;
The gateway is not multithreaded and cannot support shared database links. Each gateway session spawns a separate gateway process and connections cannot be shared.
See Also:Appendix B, "Supported SQL Syntax and Functions" for more information about restrictions on SQL syntax.
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:
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.
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.