Oracle® Database Gateway for ODBC User's Guide 11g Release 2 (11.2) Part Number E12070-03 |
|
|
PDF · Mobi · ePub |
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.
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 youCOMMIT
after 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 theDBMS_HS_PASSTHROUGH
package.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:
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
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.
This section lists restrictions on the following SQL syntax:
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.
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.
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.