ODBC Connection with Oracle

Oracle Database Gateway for ODBC

Oracle Database Gateway for ODBC is intended for low-end data integration solutions requiring the dynamic query capability to connect from an Oracle database to non-Oracle systems. Any data source compatible with the ODBC standards described in this document can be accessed using Oracle Database Gateway for ODBC. The capabilities, SQL mappings, data type conversions, and interface to the remote non-Oracle system are contained in the gateway. The gateway interacts with Heterogeneous Services to provide the transparent connectivity between Oracle and non-Oracle systems.

Oracle Database Gateway for ODBC Architecture

To access the non-Oracle data store using Oracle Database Gateway for ODBC, the gateway works with an ODBC driver. The driver that you use must be on the same machine as the gateway. The non-Oracle system can reside on the same machine as the Oracle database or on a different machine. The gateway can be installed on the machine running the non-Oracle system, the machine running the Oracle database or on a third machine as a standalone. Each configuration has its advantages and disadvantages. The issues to consider when determining where to install the gateway are network traffic, operating system platform availability, hardware resources and storage.

Note: The ODBC driver may require non-Oracle client libraries even if the non-Oracle database is located on the same machine. Refer to your ODBC driver documentation for information about the requirements for the ODBC driver.

Oracle & Non-Oracle Systems on separate machine:

In this configuration:

1.    A client connects to the Oracle database through Oracle Net.

2.    The Heterogeneous Services component of the Oracle database connects through Oracle Net to the gateway

3.    The gateway communicates with the following non-Oracle components

An ODBC driver manager

An ODBC driver

4.    Each user session receives its own dedicated agent process spawned by the first use in that user session of the database link to the non-Oracle system. The agent process ends when the user session ends.

Oracle & Non-Oracle Systems on same machine:

In this configuration:

1.    A client connects to the Oracle database through Oracle Net.

2.    The Heterogeneous Services component of the Oracle database connects through Oracle Net to the gateway

3.    The agent communicates with the following non-Oracle components:

An ODBC driver manager

An ODBC driver

4.    The driver then allows access to the non-Oracle data store.

5.    Each user session receives its own dedicated agent process spawned by the first use in that user session of the database link to the non-Oracle system. The agent process ends when the user session ends.

ODBC Connectivity Requirements

To use Oracle Database Gateway for ODBC, you must have an ODBC driver installed on the same machine as the gateway. The ODBC driver manager and driver must meet the following requirements:

The following ODBC catalog functions must work inside a transaction:

– SQLColumns

– SQLForeignKeys

– SQLGetFunctions

– SQLGetInfo

– SQLGetTypeInfo

– SQLPrimaryKeys

– SQLProcedureColumns

– SQLProcedures

– SQLStatistics

– SQLTables

On Windows:

·         The ODBC driver must have compliance level to ODBC standard 3.0. For multi-byte support, the driver needs to meet ODBC standard 3.5.

·         The ODBC driver and driver manager must conform to ODBC application program interface (API) conformance Level 1 or higher. If the ODBC driver or driver manager does not support multiple active ODBC cursors, the complexity of SQL statements that you can execute using Oracle Database Gateway for ODBC is restricted.

On UNIX:

·         The ODBC driver manager must be installed on the same machine.

·         The ODBC driver must have compliance level to ODBC Standard 3.0 and have a conformance level 1 or higher. If the ODBC driver works with an ODBC driver manager, the ODBC driver manager must be compliant with ODBC Standard 3.0 or higher. The ODBC driver must have compliance level to ODBC standard 3.0. For multi-byte support, the driver needs to meet ODBC standard 3.5.

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

Known Restrictions

Known Problems

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.

Known Restrictions

The following section describes the known restrictions:

1.    BLOB and CLOB data cannot be read by pass-through queries

2.    Updates or deletes that include unsupported functions within a WHERE clause are not allowed.

3.    Does not support stored procedures

4.    Cannot participate in distributed transactions; they support single-site transactions only

5.    Does not support multithreaded agents

6.    Does not support updating LONG columns with bind variables

7.    Does not support rowids

8.    COMMIT or ROLLBACK in PL/SQL Cursor Loops Closes Open Cursors

9.    SQL Syntax.

This section lists restrictions on the following SQL syntax:

WHERE CURRENT OF Clause

CONNECT BY Clause

ROWID

EXPLAIN PLAN Statement

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

Encrypted Format Login

Oracle database no longer supports the initialization parameter DBLINK_ENCRYPT_LOGIN. 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.

Date Arithmetic

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

date + number

number + date

date - number

date1 - date2

Supported SQL Syntax and Functions

Supported SQL Statements

Oracle Database Gateway for ODBC supports the following statements, but only if the ODBC driver and non-Oracle system can execute them and if the statements contain supported Oracle SQL functions:

DELETE

INSERT

SELECT

UPDATE

With a few exceptions, the gateway provides full support for Oracle DELETE, INSERT, SELECT, and UPDATE statements. The gateway does not support Oracle data definition language (DDL) statements. No form of the Oracle ALTER, CREATE, DROP, GRANT, or TRUNCATE statements can be used. Instead, for ALTER, CREATE, DROP, and GRANT statements, use the pass-through feature of the gateway if you need to use DDL statements against the non-Oracle system database.

Oracle Functions

All functions are evaluated by the non-Oracle system after the gateway has converted them to the native SQL. Only a limited set of functions are assumed to be supported by the non-Oracle system. Most Oracle functions have no equivalent function in this limited set. Consequently, although post-processing is performed by the Oracle database, many Oracle functions are not supported by Oracle Database Gateway for ODBC, possibly impacting performance.

If an Oracle SQL function is not supported by Oracle Database Gateway for ODBC, this function is not supported in DELETE, INSERT, or UPDATE statements. In SELECT statements, these functions are evaluated by the Oracle database and processed after they are returned from the non-Oracle system. If an unsupported function is used in a DELETE, INSERT, or UPDATE statement, it generates the following Oracle error:

ORA-02070: database db_link_name does not support function in this context

Oracle Database Gateway for ODBC assumes that the following minimum set of SQL functions is supported by the ODBC driver provider that is being used:

AVG(exp)

LIKE(exp)

COUNT(*)

MAX(exp)

MIN(exp)

NOT