3
Features of Oracle Transparent Gateways and Generic Connectivity

This chapter describes the major features provided by Oracle Transparent Gateways and Generic Connectivity. Description s of these features are contained in the following topics:

• SQL and PL/SQL Support
• Heterogeneous Replication
• Pass-Through SQL
• Result Set Support
• Data Dictionary Translations
• Datetime Datatypes
• < a href="majfeat.htm#1006691">Two-Phase Commit Protocol
• Piecewise Long
• SQL*Plus DESCRIBE Command
• Constraints on SQL in a Distributed Environment
• Using Oracle's Optimizer with He terogeneous Services

  Note: These features may not be available in all Heterogeneous Services based gateways. Not only must there be generic support for these features, which Heterogeneous Services provides, but there must also be support added to the driver for them. Please consult your gateways documentation to determine if a particular feature is supported for your gateway.

SQL and PL/SQL Support

SQL statements are translated and datatypes are mapped according to capabilities. PL/SQL calls are mapped to non-Oracle system stored procedures. In the case of SQL statements, if functionality is missing at the remote system, then either a simpler que ry is issued or the statement is broken up into multiple queries and the desired results are obtained by post processing in the Oracl e database.

Even though Heterogeneous Services can, for the most part, incorporate non-Orac le systems into Oracle distributed sessions, there are several limitations to this. Some of the generic limitations are:

• There is no support for CONNECT BY clauses in SQL statements.
• ROWID support is limited; consult individual gateway documentation for more d etails. The Oracle Universal ROWID datatype is not supported in any gateway that uses Heterogeneous Services.
• LOBs, ADTs, and REFs are not supported.
• Remote packages are not supported.
• Remote stored procedures can have out arguments of type REF CURSOR but not in or in-out objects.
• Oracle Heterogeneous Services agents do not support shared database links.

  Note: In addition to these generic limitations, each gateway can have additional limitations. Please consult the gateway documentation for individual gate ways for a complete list of limitations of the product.

Heterogeneous Replication

Data can be replicated between a non-Oracle system and an Oracle server using materialize d views.

Materialized views instantiate data captured from tables at the non-Oracle master site at a particular point in time. This instant is defined by a refresh operation, which copies this data to the Oracle server and synchronizes the copy on Oracle with the master copy on the non-Oracle system. The "materialized" data is then available as a view on the Oracle server.

Replication facilities provide mechanisms to schedule refreshes and to collect materialized views into replication groups to facilitate their administration. Refresh groups permit refreshing multiple materialized views just a s if they were a single object.

Heterogeneous replication support is necessarily limited to a subset of the full Oracle-to-Oracle replication functionality:

• Only the non-Oracle system can be the master site. This is because materialized views can be created only on an Oracle server.
• Materialized views must use complete refresh. This is because fast refresh would require Oracle-specific functionality in the non-Oracle system.
• Not all types of materialized views can be created to reference tables on a non-Oracle system. Primary key and subquery materialized views are sup ported, but rowid and object id materialized views are not supported. This is because there is no SQL standard for the format and con tents of rowids, and non-Oracle systems do not implement Oracle objects.

Other restri ctions apply to any access to non-Oracle data through Oracle's Heterogeneous Services facilities. The most important of these are:

• Non-Oracle datatypes in table columns mapped to a fixed view mus t be compatible with (that is, have a mapping to or from) Oracle datatypes. This is usually true for datatypes defined by ANSI SQL st andards.
• A subquery materialized view may not be able to use language features restricted by individual non-Oracle systems. In many cases Heterogeneous Services supports such language features by processing quer ies within the Oracle server, but occasionally the non-Oracle systems impose limitations that cannot be diagnosed until Heterogeneous Services attempts to execute the query.

The following examples illustrate basic setu p and use of three materialized views to replicate data from a non-Oracle system to an Oracle data store.

Example 1: Create materialized views for heterogeneous replication

This example creates three materialized views that are then used in succeeding examples.

1. Create a primary key materialized view of table customer@remote_db.

       CREATE MATERIALIZED VIEW pk_mv REFRESH COMPLETE AS
         SELECT * FROM customer@remote_db
    WHERE "zip" = 94555;
   
   

2. Create a subquery materialized view of tables orders@remote_db and customer@remote_db.

       CREATE MATERIALIZED VIEW sq_mv REFRESH COMPLETE AS
         SELECT * FROM orders@remote_db o WHERE EXISTS
           (SELECT c."c_id" FROM customer@remote_db c
              WHERE c."zip" = 94555 and c."c
   _id"  = o."c_id" );
   
   

3. Create a complex mat erialized view of data from multiple tables on remote_db.

   
      CREATE MATERIALIZED VIEW cx_mv
         REFRESH COMPLETE AS
         SELECT  c."c_id", o."o_
   id"
           FROM customer@remote_db c,
                orders@remote_db o,
                order_line@remote_db ol
           WHERE c."c_id" = o."c_id"
           AND o
   ."o_id" = ol."o_id";
   

Example 2: Se t up a refresh group for heterogeneous replication

BEGIN
  db
ms_refresh.make('refgroup1',
   'pk_mv, sq_mv, cx_mv',
   NULL, NULL);

 END;
 /

Example 3: Force refresh of all 3 materialized views

BEGIN
   dbms_refresh.refresh('refgroup1');
END;
 /

Pass-Through SQL

The pass-through SQL feature enables you to send a statement directly to a non- Oracle system without being interpreted by the Oracle server. This feature can be useful if the non-Oracle system allows for operatio ns in statements for which there is no equivalent in Oracle.

This section contains the foll owing topics:

• Using the DBMS_HS_ PASSTHROUGH Package
• Considering the Implicat ions of Using Pass-Through SQL
• Executing Pas s-Through SQL Statements

Using the DBMS_HS_PASSTHROUGH Package

You can execute pass-through SQL statements directly at 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 at the non-Oracle system. In reality, however, calls to this package are intercepted by Heterogeneous Services and mapped onto one or more Heterogeneous Services application programming interface (API) calls. The driver, in turn, maps these Heterogeneous Services API calls onto the API of the non-Oracle system. The client application should invoke the procedures in the package through a database link in exactly 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.

Considering the Implications o f Using Pass-Through SQL

When you execute a pass-through SQL stateme nt that implicitly commits or rolls back a transaction in the non-Oracle system, the transaction is affected. For example, some syste ms implicitly commit the transaction containing a data definition language (DDL) statement. Because the Oracle database server is byp assed, the Oracle database server is unaware of the commit in the non-Oracle system. Consequently, the data at the non-Oracle system can be committed while the transaction in the Oracle database server is not.

If the transac tion in the Oracle database server is rolled back, data inconsistencies between the Oracle database server and the non-Oracle server can occur. This situation results in global data inconsistency.

Note that if the application executes a regular COMMIT statement, the Oracle database server can coordinate the distr ibuted transaction with the non-Oracle system. The statement executed with the pass-through facility is part of the distributed trans action.

E xecuting Pass-Through SQL Statements

The following table shows the f unctions and procedures provided by the DBMS_HS_PASSTHROUGH package that enable you to execute pass-through SQL statemen ts.

Executing Non- Queries

Non-queries include the following statements and types of st atements:

• INSERT
• UPDATE
• DELETE
• DDL

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

DECLARE
  num_rows INTE
GER;

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

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

Using Bind Variables: Overview

Bind variables allow you to 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 need to insert four rows in a particular 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 pass-through SQL statements with bind variables, you must:

1. Open a cursor.
2. Parse the SQL statement at the non-Oracle system.
3. Bind the variables.
4. Execute the SQL statement at the non-Oracle system.
5. Close the cursor.

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

Figure 3-1 Flow Diagram for Non-Query Pass-Through SQL

Text descri ption of the illustration heter007.gif

Using IN Bind Variables

The syntax of the non-Oracle system determines how a statement specifies a bind variable. For example, in Oracle 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. In non-Oracle systems, you may need to s pecify bind variables with a question mark. For example:

...
UPDATE e
mp
SET sal=sal*1.1
WHERE ename= ?;
...

In the bind variable step, you must positionally associate host program variables (in this ca se, PL/SQL) with each of these bind variables.

For example, to execute the preceding statem ent, you can use the following PL/SQL program:

DECLARE
  c INTEGER;
<
a name="1006208">  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.EX
ECUTE_NON_QUERY@salesdb(c);
  DBMS_OUTPUT.PUT_LINE(nr||' rows updated');
  DBMS_HS_PASSTH
ROUGH.CLOSE_CURSOR@salesdb(c);
END;
/

In some cases, the non-Oracle system can also support OUT bind variables. With OU T bind variables, the value of the bind variable is not known until after the execution of the SQL sta tement.

Although OUT bind variables are populated after the SQL statement is e xecuted, the non-Oracle system must know that the particular bind variable is an OUT bind variable be fore 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 the SQL statement is executed, you can retrieve the value of the OUT bind variable using the GET_VALUE procedure.

Using IN OUT Bind Variables

A bind variable can be both an IN and an OUT variable. This mea ns that the value of the bind variable must be known before the SQL statement is executed but can be changed after the SQL statement is executed.

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

Executing Que ries

The difference between queries and non-queries is that queries retrieve a result set from a SELECT statement. The result set is retrieved by iterating over a cursor.

Figure 3-2 illustrates the steps in a pass-through SQL query. After t he system parses the SELECT statement, each row of the result set can be fetched with the FETCH_ROW procedu re. After the row is fetched, use the GET_VALUE procedure to retrieve the select list items into program variables. Afte r all rows are fetched, you can close the cursor.

Figure 3-2 Pass-Through SQL for Queries

Text description of the illustration heter008.gif

You do not have to fetch all the rows. You can close the cursor at any time after opening the cursor, for exam ple, after fetching a few rows.

The next ex ample executes a query:

DECLARE
   val  VARCHAR2(100);
   c    INTEGER;
   nr   INTEGER;
BEGIN
  c := DBMS_HS_PASS
THROUGH.OPEN_CURSOR@salesdb; 
  DBMS_HS_PASSTHROUGH.PARSE@salesdb(c, 
    'select ENAME
<
a name="1006267">     from   EMP
     where  DEPTNO=10');
  LOOP
    nr := DBMS_HS_PASSTHROUGH.FETCH_ROW@salesdb(c);
    EXIT WHEN nr = 0;
    DBMS_HS_P
ASSTHROUGH.GET_VALUE@salesdb(c, 1, val);
    DBMS_OUTPUT.PUT_LINE(val);
  END LOOP;  
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@salesdb(c); 
END;
/

After the SELECT statementhas been parsed, the rows are fetched and printed in a loop unti l the function FETCH_ROW returns the value 0.

Result Set Support

Various relational databases allow stored procedures to return result sets. In other words, stored procedures will be able to return one or more sets of rows.

Traditionally, database stored procedures worked exactly like pr ocedures in any high-level programming language. They had a fixed number of arguments which could be of types in, out, or in-out. If a procedure had n arguments, it could return at most n values as res ults. However, suppose that somebody wanted a stored procedure to execute a query such as SELECT * FROM emp and return t he results. The emp table might have a fixed number of columns but there is no way of telling, at procedure creation tim e, the number of rows it has. Because of this, no traditional stored procedure can be created that can return the results of a such a query. As a result, several relational database vendors added the capability of returning results sets from stored procedures, but e ach kind of relational database returns result sets from stored procedures in a different way.

Oracle has a datatype called a REF CURSOR. Like every other Oracle datatype, a stored procedure can take this dataty pe as an in or out argument. In Oracle, a stored procedure can return a result set in the following way. To return a result set, a st ored procedure must have an output argument of type REF CURSOR. It then opens a cursor for a SQL statement and places a handle to that cursor in that output parameter. The caller can then fetch from the REF CURSOR the same way as from any o ther cursor.

Oracle can do a lot more than simply return result sets. REF CURSORs can be passed as input arguments to PL/SQL routines to be passed back and forth between client programs and PL/SQL routines or be tween several PL/SQL routines.

In this model, there is no pre-specified limit to the number of result sets that can be returned by a store d procedure. Both Model 1 and Oracle have a limit. For Oracle 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 proce dure language. Another way that Model 2 differs from Oracle and Model 1 is that they do not return a handle to the result sets but in stead place the entire result set on the wire when returning from a stored procedure. For Oracle, 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 and Model 1, once the handle is obtained, data from the result set is obtained by doing a fetch on the handle; we have a bunch of cursors open an d can fetch 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 or der determined by the stored procedure and the output arguments of the procedures coming at the end. So the whole of the first result set must be fetched, then the whole of the second one, until all of the results have been fetched. Finally, the stored procedure out arguments must be fetched.

Heterogeneous Services Support for Result Sets

As can be seen in the preceding sections, result set support exists among non-Oracle databases in a variety of forms. All of these have to be mapped onto the Oracle REF CURSOR model. Due to the considerable differences in behavior among the v arious non-Oracle systems, Heterogeneous Services result set support will have to behave in one of two different ways depending on th e non-Oracle system it is connected to.

Please note the following about Heterogeneous Servi ces result set support:

• Result set support is present in Heter ogeneous Services generic code but in order for the feature to work in a gateway, the driver has to implement it as well. Not all dri vers have implemented result set support and the customer must check in his gateway-specific documentation to determine whether it is supported in that gateway.
• Heterogeneous Services will support REF CURS OR out arguments from stored procedures. In and in-out arguments will not be supported.
• The REF CURSOR out arguments will all be anonymous REF CURSORs. No typed REF CURSORs are returned by Heterogeneous Services.

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

INSERT INTO emp@remote_db SELECT * FROM emp;

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

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;
/

DELETE FROM emp@remote_db WHERE hiredate > sysdate;

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

DELETE FROM emp@remote_db WHERE hiredate > :1;