3 Features of Oracle Database Gateways

3.3.1 Using the DBMS_HS_PASSTHROUGH Package

You can execute passthrough SQL statements directly on the non-Oracle system using the PL/SQL package DBMS_HS_PASSTHROUGH. Any statement executed with this package is executed in the same transaction as standard SQL statements.

The DBMS_HS_PASSTHROUGH package is a virtual package. It conceptually resides on the non-Oracle system. In reality, however, calls to this package are intercepted by Heterogeneous Services and are mapped to one or more Heterogeneous Services calls. The driver then maps these Heterogeneous Services calls to the API of the non-Oracle system. The client application invokes the procedures in the package through a database link in the same way as it would invoke a non-Oracle system stored procedure. The special processing done by Heterogeneous Services is transparent to the user.

3.3.2 Considering the Implications of Using Passthrough SQL

When you execute a passthrough SQL statement that implicitly commits or rolls back a transaction in a non-Oracle system, the transaction is affected. For example, some systems implicitly commit the transaction containing a data definition language (DDL) statement. Because Oracle Database is bypassed, Oracle Database is unaware that a transaction was committed in the non-Oracle system. Consequently, the data at the non-Oracle system can be committed, while the transaction in Oracle Database is not.

If the transaction in Oracle Database is rolled back, data inconsistencies between Oracle Database and the non-Oracle system can occur. This situation results in global data inconsistency.

Note that if the application executes a typical COMMIT statement, Oracle Database can coordinate the distributed transaction with the non-Oracle system. The statement executed with the passthrough facility is part of the distributed transaction.

3.3.3 Executing Passthrough SQL Statements

The following table shows the functions and procedures provided by the DBMS_HS_PASSTHROUGH package that enable you to execute passthrough SQL statements.

3.3.3.1 Executing Nonqueries

Nonqueries include the following statements and types of statements:

• INSERT

• UPDATE

• DELETE

• DDL

To execute nonquery statements, use the EXECUTE_IMMEDIATE function. For example, to execute a DDL statement on a non-Oracle system that you can access using the database link salesdb, enter the following:

DECLARE
  num_rows INTEGER;

BEGIN
  num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@salesdb
            ('CREATE TABLE dept1 (n SMALLINT, loc CHARACTER(10))');
END;

The variable num_rows is assigned the number of rows affected by the statements that were executed. For DDL statements, zero is returned. Note that you cannot execute a query with EXECUTE_IMMEDIATE function and you cannot use bind variables.

3.3.3.1.1 Using Bind Variables: Overview

Bind variables let you use the same SQL statement multiple times with different values, reducing the number of times a SQL statement needs to be parsed. For example, when you insert four rows in a table, you can parse the SQL statement once, and bind and execute the SQL statement for each row. One SQL statement can have zero or more bind variables.

To execute passthrough SQL statements with bind variables, you must:

1. Open a cursor.

2. Parse the SQL statement on the non-Oracle system.

3. Bind the variables.

4. Execute the SQL statement on the non-Oracle system.

5. Close the cursor.

Figure 3-1 shows the flow diagram for executing nonqueries with bind variables.

Figure 3-1 Flow Diagram for Nonquery Passthrough SQL

Description of "Figure 3-1 Flow Diagram for Nonquery Passthrough SQL"

3.3.3.1.2 Using IN Bind Variables

The syntax of the non-Oracle system determines how a statement specifies a bind variable. For example, on an Oracle system you define bind variables with a preceding colon. For example:

...
UPDATE emp
SET sal=sal*1.1
WHERE ename=:ename;
...

In this statement, ename is the bind variable. On non-Oracle systems, you may need to specify bind variables with a question mark. For example:

...
UPDATE emp
SET sal=sal*1.1
WHERE ename= ?;
...

In the bind variable step, you must positionally associate host program variables (in this case, PL/SQL) with each of these bind variables. For example, to execute the preceding statement, use the following PL/SQL program:

DECLARE
  c INTEGER;
  nr INTEGER;
BEGIN
  c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@salesdb;
  DBMS_HS_PASSTHROUGH.PARSE@salesdb(c,
        'UPDATE emp SET SAL=SAL*1.1 WHERE ename=?');
  DBMS_HS_PASSTHROUGH.BIND_VARIABLE@salesdb(c,1,'JONES');
  nr:=DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@salesdb(c);
  DBMS_OUTPUT.PUT_LINE(nr||' rows updated');
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@salesdb(c);
END;

3.3.3.1.3 Using OUT Bind Variables

The non-Oracle system can support OUT bind variables. With OUT bind variables, the value of the bind variable is not known until after the execution of the SQL statement.

Although OUT bind variables are populated after executing the SQL statement, the non-Oracle system must know that the particular bind variable is an OUT bind variable before the SQL statement is executed. You must use the BIND_OUT_VARIABLE procedure to specify that the bind variable is an OUT bind variable.

After executing the SQL statement, you can retrieve the value of the OUT bind variable using the GET_VALUE procedure.

3.3.3.1.4 Using IN OUT Bind Variables

A bind variable can be both an IN and an OUT variable. This means that the value of the bind variable must be known before executing the SQL statement, but you can change the value after the SQL statement is executed.

For IN OUT bind variables, you must use the BIND_INOUT_VARIABLE procedure to provide a value before executing the SQL statement. After executing the SQL statement, you must use the GET_VALUE procedure to retrieve the new value of the bind variable.

3.3.3.2 Executing Queries

The difference between queries and nonqueries is that queries retrieve a result set from a SELECT statement. The result set is retrieved by using a cursor.

Figure 3-2 illustrates the steps in a passthrough SQL query. After the system parses the SELECT statement, each row of the result set can be retrieved with the FETCH_ROW procedure. After the row is retrieved, use the GET_VALUE procedure to retrieve the selected list of items into program variables. After all rows are retrieved, you can close the cursor.

Figure 3-2 Passthrough SQL for Queries

Description of "Figure 3-2 Passthrough SQL for Queries"

You do not have to retrieve all the rows. You can close the cursor at any time after opening the cursor.

Note:

Although you are retrieving one row at a time, Heterogeneous Services optimizes the round-trips between Oracle Database and the non-Oracle system by buffering multiple rows and fetching from the non-Oracle data system in one round-trip.

The following example executes a query:

DECLARE
   val  VARCHAR2(100);
   c    INTEGER;
   nr   INTEGER;
BEGIN
  c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@salesdb; 
  DBMS_HS_PASSTHROUGH.PARSE@salesdb(c, 
    'select ENAME
     from   EMP
     where  DEPTNO=10');
  LOOP
    nr := DBMS_HS_PASSTHROUGH.FETCH_ROW@salesdb(c);
    EXIT WHEN nr = 0;
    DBMS_HS_PASSTHROUGH.GET_VALUE@salesdb(c, 1, val);
    DBMS_OUTPUT.PUT_LINE(val);
  END LOOP;  
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@salesdb(c); 
END;

After the SELECT statement has been parsed, the rows are fetched and printed in a loop until the FETCH_ROW function returns the value 0.

3.4.1 Result Set Support for Non-Oracle Systems

Several non-Oracle systems allow stored procedures to return result sets, but they do so in different ways. Result set support for non-Oracle databases is typically based on one of the following two models.

• Model 1: Result Set Support

  When creating a stored procedure, you can explicitly specify the maximum number of result sets that can be returned by that stored procedure. While executing, the stored procedure can open anywhere from zero up to its specified maximum number of result sets. After the execution of the stored procedure, a client program gets handles to these result sets by using either an embedded SQL directive or by calling a client library function. After that, the client program can retrieve from the result set in the same way as from a typical cursor.

• Model 2: Result Set Support

  In this model, there is no specified limit to the number of result sets that can be returned by a stored procedure. Both Model 1 and Oracle Database have a limit. For Oracle Database, the number of result sets returned by a stored procedure can be at most the number of REF CURSOR OUT arguments. For Model 1, the upper limit is specified using a directive in the stored procedure language. Another way that Model 2 differs from Oracle Database and Model 1 is that they do not return a handle to the result sets. Instead, they place the entire result set on the wire when returning from a stored procedure. For Oracle Database, the handle is the REF CURSOR OUT argument. For Model 1, it is obtained separately after the execution of the stored procedure. For both Oracle Database and Model 1, after the handle is obtained, data from the result set is obtained by doing a fetch on the handle; there are several cursors open and the fetch can be in any order. In the case of Model 2, however, all the data is already on the wire, with the result sets coming in the order determined by the stored procedure and the output arguments of the procedures coming at the end. The entire first result set must be retrieved, then the entire second result set, until all of the results are retrieved. Finally, the stored procedure OUT arguments are retrieved.

3.4.2 Heterogeneous Services Support for Result Sets

Result set support exists among non-Oracle databases in different forms. All of these must be mapped to the Oracle REF CURSOR model. Due to the differences in behavior among the non-Oracle systems, Heterogeneous Services result set support acts in one of two different ways depending on the non-Oracle system to which it is connected.

Note the following about Heterogeneous Services result set support:

• Result set support is part of the Heterogeneous Services generic code, but for the feature to work in a gateway, the driver has to implement it. Not all drivers have implemented result set support and you must verify that your gateway is supported.

• Heterogeneous Services supports REF CURSOR OUT arguments from stored procedures. IN and IN OUT arguments are not supported.

• The REF CURSOR OUT arguments are all anonymous reference cursors. REF CURSORs that are returned by Heterogeneous Services do not have types.

3.10.1 Remote and Heterogeneous References

A statement can, with restrictions, be executed on any database node referenced in the statement or the local node. If all objects referenced are resolved to a single, referenced node, Oracle attempts to execute a query at that node. You can force execution at a referenced node by using the /*+ REMOTE_MAPPED */ or /*+ DRIVING_SITE */ hints. If a statement is forwarded to a node other than the node from where the statement was issued, the statement is said to be remote-mapped.

There is complete data type checking support for remote-mapped statements. The result provides consistent data type checking and complete data type coercion.

The ways in which statements can be remote-mapped are subject to specific rules or restrictions. If these rules are not followed, an error occurs. As long as the statements issued are consistent with all these rules, the order in which the rules are applied does not matter. See Section 3.10.2 for these rules or restrictions.

Different constraints exist when you are using SQL for remote mapping in a distributed environment. This distributed environment can include remote Oracle databases as well as non-Oracle databases that are accessed through Oracle Database gateways.

3.10.2 Important Restrictions

The following section lists some of the different rules or restrictions that exist when you are using SQL for remote mapping in a distributed environment.

Rule A: A data definition language statement cannot be remote-mapped.

In Oracle data definition language, the target object syntactically has no place for a remote reference. Data definition language statements that contain remote references are always executed locally. For Heterogeneous Services, this means it cannot directly create database objects in a non-Oracle database using SQL.

However, there is an indirect way using passthrough SQL as shown in the following example:

DECLARE
  num_rows INTEGER;
BEGIN 
  num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@remote_db 
  (
     'create table x1 (c1 char, c2 int)'
  ); 
END; 

Rule B: INSERT, UPDATE and DELETE statements with a remote target table must be remote-mapped.

This rule is more restrictive for non-Oracle remote databases than for a remote Oracle database. This is because the remote system cannot fetch data from the originating Oracle database while executing data manipulation language (DML) statements targeting tables in a non-Oracle system.

For example, to insert all local employees from the local emp table to a remote non-Oracle emp table, use the following statement:

INSERT INTO emp@remote_db SELECT * FROM emp;

This statement is remote-mapped to the remote database. The remote-mapped statement sent to the remote database contains a remote reference back to the originating database for emp. Such a remote link received by the remote database is called a callback link.

If callback links are not supported by a particular gateway, the previous INSERT statements returns the following error:

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

The workaround is to write a PL/SQL block. For example:

DECLARE 
CURSOR remote_insert IS SELECT * FROM emp;
BEGIN 
   FOR rec IN remote_insert LOOP
    INSERT INTO emp@remote_db (empno, ename, deptno) VALUES (
      rec.empno,
      rec.ename, 
      rec.deptno 
    );  
   END loop;
END;

Another special case involves session-specific SQL functions such as USER, USERENV, and SYSDATE. These functions need to be executed at the originating site. A remote-mapped statement containing these functions contains a callback link. For a non-Oracle database for which callbacks are not supported, this can (by default) result in a restriction error.

For example, consider the following statement:

DELETE FROM emp@remote_db WHERE hiredate > sysdate;

The previous statement returns the following error message:

ORA-02070: database REMOTE_DB does not support special functions in this context 

This can be resolved by replacing special functions with a bind variable. For example:

DELETE FROM emp@remote_db WHERE hiredate > :1;

Rule C: Object features like tables with nested table columns, ADT columns, Opaque columns, or Ref Columns cannot be remote-mapped.

Currently, these column types are not supported for heterogeneous access. Hence, this limitation is not directly encountered.

Rule D: SQL statements containing operators and constructs that are not supported at the remote site cannot be remote-mapped.

In the case of an INSERT, UPDATE, or DELETE, this means that the SQL statement cannot be executed (see Rule B). However, you might still be able to execute the SQL statement if the unsupported operator or construct can be executed through a callback link.

In the case of a SELECT, you can always execute a statement affected by this rule as long as none of the remaining rules require the statement to be remote mapped. The SELECT statements affected by this rule are executed by fetching all the necessary data through a remote SELECT operation, and processing the unsupported operator or construct locally using the local SQL engine.

A remote SELECT operation is the operation that retrieves rows from the remote table as opposed to an operation that retrieves data from the local table. In the worse possible case of a remote SELECT, Oracle does a full table scan. A full table scan is a fetch of all the data in the remote table across the network without any filtering (for example, SELECT * FROM EMP).

Full table scans are expensive and, therefore, Oracle attempts to avoid them. If there are indexes on the remote table that can be used, these indexes are used in a WHERE clause predicate to reduce the number of rows fetched across the network.

You can check the SQL statement generated by Oracle Database by explaining the statement and querying the OTHER column of the explain plan table for each REMOTE operation.

For example, consider the following statement:

SELECT COUNT(*) FROM emp@remote_db WHERE hiredate < sysdate;

The statement returns the following output:

COUNT(*)  
----------
        14
1 row selected.

The remote table scan is:

SELECT hiredate FROM emp;

The predicate converted to a filter cannot be generated back and passed down to the remote operation because sysdate is not supported by the remote_db or evaluation rules. Thus sysdate must be executed locally.

Rule E: SQL statement containing a table expression cannot be remote-mapped.

This limitation is not directly encountered because table expressions are not supported in the heterogeneous access module.

Rule F: If a SQL statement selects LONG data, the statement must be mapped to the node where the table containing the LONG data resides.

Consider the following type of statement:

SELECT long1 FROM table_with_long@remote_db, dual; 

The previous statement returns the following error message (if callback links are not supported):

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

Rule G: The statement must be mapped to the node on which the table or tables with columns referenced in the FOR UPDATE OF clause resides when the SQL statement is of form "SELECT...FOR UPDATE OF..."

When the SQL statement is of the form SELECT...FOR UPDATE OF..., the statement must be mapped to the node on which the table or tables with columns referenced in the FOR UPDATE OF clause resides.

For example, consider the following statement:

SELECT ename FROM emp@remote_db WHERE hiredate < sysdate FOR UPDATE OF empno;

The previous statement returns the following error message if it cannot be remote-mapped:

ORA-02070: database REMOTE_DB does not support special functions in this context 

Rule H: If the SQL statement contains a SEQUENCE or sequences, then the statement must be mapped to the site where each sequence resides.

This rule is not encountered for the heterogeneous access module because remote non-Oracle sequences are not supported.

Rule I: If the statement contains a user-defined operator or operators, then the statement must be mapped to the node where each operator is defined.

If the statement contains a user-defined operator, the entire statement needs to be remote-mapped to the database node where the operator is defined.

Rule J: A statement containing duplicate bind variables cannot be remote-mapped.

The workaround for this restriction is to use unique bind variables and bind by number.

3.11.1 Example: Using Index and Table Statistics

Consider the following statement where you create a table in the Oracle database with 10 rows:

CREATE TABLE T1 (C1 number);

Analyze the table using the DBMS_STATS package. For example:

DBMS_STATS.GATHER_TABLE_STATS ('SCOTT','T1');
DBMS_STATS.GENERATE_STATS ('SCOTT','T1');

The preceding example assumes the schema name is SCOTT and the table name is T1. See the Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_STATS package.

Create a table in the non-Oracle system with 1000 rows.

Issue the following SQL statement:

SELECT a.* FROM remote_t1@remote_db a, T1 b 
    WHERE a.C1 = b.C1;

The Oracle optimizer issues the following SQL statement to the agent:

SELECT C1 FROM remote_t1@remote_db;

This fetches all 1000 rows from the non-Oracle system and performs the join in the Oracle database.

If we add a unique index on the column C1 in the table remote_t1, and issue the same SQL statement again, the agent receives the following SQL statement for each value of C1 in the local t1:

...
SELECT C1 FROM remote_t1@remote_db WHERE C1 = ?;
...

To verify the SQL execution plan, generate an explain plan for the SQL statement. First, load utlxplan in the admin directory.

Enter the following:

EXPLAIN PLAN FOR SELECT a.* FROM remote_t1@remote_db a, T1 b 
    WHERE a.C1 = b.C1;

Execute the utlxpls utility script by entering the following statement.

@utlxpls

OPERATION REMOTE indicates that remote SQL is being referenced.

To find out what statement is sent, enter the following statement:

SELECT ID, OTHER FROM PLAN_TABLE WHERE OPERATION = 'REMOTE';

3.11.2 Example: Remote Join Optimization

The following is an example of the remote join optimization capability of the Oracle database.

Consider the following example:

EXPLAIN PLAN FOR
SELECT e.ename, d.dname, f.ename, f.deptno FROM
   dept d,
   emp@remote_db e,
   emp@remote_db f
 WHERE e.mgr = f.empno
  AND e.deptno = d.deptno 
  AND e.empno = f.empno;
  
@utlxpls

You should see output similar to the following:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id    | Operation                           | Name  | Rows  | Bytes  | Cost
| Inst  |IN-OUT|
---------------------------------------------------------------------------
|   0   | SELECT STATEMENT   |                |  2000   |   197K|   205 |
|*  1   | HASH JOIN          |                |  2000   |   197K|
205 |
|   2   | TABLE ACCESS FULL  | DEPT           |  21     |   462 |     2 |
|*  3   | HASH JOIN          |                |  2000   |   154K|
201 |
|   4   | REMOTE             |                |  2000   | 66000 
|    52 |
|   5   | REMOTE             |                |  2000   | 92000
|    52 |
---------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Query Block Name / Hint Alias (identified by operation id):
-----------------------------------------------------------

   1 - sel$1 / D
   2 - sel$1 / D
   3 - sel$1 / F
   4 - sel$1 / F
   5 - sel$1 / E

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

---------------------------------------------------

   1 - access("E"."DEPTNO"="D"."DEPTNO")
   3 - access("E"."MGR"="F"."EMPNO" AND "E"."EMPNO"="F"."EMPNO")

Issue the following statement:

SET long 300
SELECT other FROM plan_table WHERE operation = 'REMOTE'; 

You should see output similar to the following:

OTHER
--------------------------------------------------------------------------------

SELECT "EMPNO","ENAME","DEPTNO" FROM "EMP"
SELECT "EMPNO","ENAME","MGR","DEPTNO" FROM "EMP"
SELECT "EMPNO","ENAME","DEPTNO" FROM "EMP"
SELECT "EMPNO","ENAME","MGR","DEPTNO" FROM "EMP"

3.11.3 Optimizer Restrictions for Non-Oracle Access

The following are optimizer restrictions for non-Oracle system access:

• There are no column statistics for remote objects. This can result in poor execution plans. Verify the execution plan and use hints to improve the plan.

• There is no optimizer hint to force a remote join. However, there is a remote query block optimization that can be used to rewrite the query slightly in order to get a remote join.

  The example from the previous section can be rewritten to the following form:

      SELECT v.ename, d.dname, d.deptno FROM dept d,
          (SELECT /*+ NO_MERGE */ 
           e.deptno deptno, e.ename ename emp@remote_db e, emp@remote_db f
               WHERE e.mgr = f.empno
               AND e.empno = f.empno;
          )
        WHERE v.deptno = d.deptno;
  
  
  

This example guarantees a remote join because it has been isolated in a nested query with the NO_MERGE hint.