Oracle Heterogeneous Services Release 8.1.7 Part Number A88714-01 |
Library | Service | Contents | Index

# 4
Developing Applications with Heterogeneous Services

This chapter provides information for application developers who want to use Heterogeneous Services.

This chapter contains these topics:

- Developing Applications with Heterogeneous Services: Overview

- Developing Applications Using Pass-Through SQL

- Optimizing Data Transfers Using Bulk Fetch

- Researching the Locking Behavior of Non-Oracle Systems

- Limitations to Heterogeneous Services

## Developing Applications with Heterogeneous Services: Overview

When you develop applications with Heterogeneous Services, Heterogeneous Services makes the non-Oracle system appear as if it were another Oracle database server.

However, you may sometimes need to access a non-Oracle system using the non-Oracle system's SQL dialect. To make access possible, Heterogeneous Services provides a pass-through SQL feature that allows you to directly execute a native SQL statement at the non-Oracle system.

Additionally, Heterogeneous Services supports bulk fetches to optimize the data transfers for large data sets between a non-Oracle system, agent and Oracle database server. This chapter also discusses how to tune such data transfers.

## Developing Applications Using Pass-Through SQL

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

This section contains the following topics:

- Using the DBMS_HS_PASSTHROUGH package

- Considering the Implications of Using Pass-Through SQL

- Executing Pass-Through SQL Statements

### Using the DBMS_HS_PASSTHROUGH package

You can execute these statements directly at the non-Oracle system using the PL/SQL package DBMS_HS_PASSTHROUGH. Any statement executed with the pass-through package is executed in the same transaction as standard SQL statements.

The DBMS_HS_PASSTHROUGH package conceptually resides at the non-Oracle system. You must invoke procedures and functions in the package by using the appropriate database link to the non-Oracle system.

See Also: Oracle8i Supplied PL/SQL Packages Reference

for more information about this package.

### Considering the Implications of Using Pass-Through SQL

When you execute a pass-through SQL statement that implicitly commits or rolls back a transaction in the non-Oracle system, the transaction is affected. For example, some systems implicitly commit the transaction containing a DDL statement. Because the Oracle database server is bypassed, 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 transaction 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 distributed transaction with the non-Oracle system. The statement executed with the pass-through facility is part of the distributed transaction.

### Executing Pass-Through SQL Statements

Table 4-1 shows the functions and procedures provided by the DBMS_HS_PASSTHROUGH package that allow you to execute pass-through SQL statements.

##### Table 4-1 DBMS_HS_PASSTHROUGH Procedures and Functions

Procedure/Function | Description
--- | ---
OPEN_CURSOR | Opens a cursor
CLOSE_CURSOR | Closes a cursor
PARSE | Parses the statement
BIND_VARIABLE | Binds IN variables
BIND_OUT_VARIABLE | Binds OUT variables
BIND_INOUT_VARIABLE | Binds IN OUT variables
EXECUTE_NON_QUERY | Executes non-query
EXECUTE_IMMEDIATE | Executes non-query without bind variables
FETCH_ROW | Fetches rows from query
GET_VALUE | Retrieves column value from SELECT statement or retrieves OUT bind parameters

This section contains these topics:

- Executing Non-Queries

- Executing Queries

#### Executing Non-Queries

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

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

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

The variable num_rows is assigned 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, if you need to insert four rows in a particular table, then 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 4-1 shows the flow diagram for executing non-queries with bind variables.

#### Figure 4-1 Flow Diagram for Non-Query Pass-Through SQL

Text description of the illustration dds81046.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, as in:

    UPDATE EMP
    SET SAL=SAL*1.1
    WHERE ENAME=:ename

In this statement `:ename` is the bind variable. In other non-Oracle systems you might need to specify bind variables with a question mark, as in:

    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 previous statement, you can 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(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;

##### Using OUT Bind Variables

In some cases, the non-Oracle system can also 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 the SQL statement is executed, 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 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 means 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_VARIABLE 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 Queries

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 4-2 illustrates the steps in a pass-through SQL query. After the system parses the SELECT statement, each row of the result set can be fetched with the FETCH_ROW procedure. After the row is fetched, use the GET_VALUE procedure to retrieve the select list items into program variables. After all rows are fetched you can close the cursor.

#### Figure 4-2 Pass-through SQL for Queries

Text description of the illustration dds81047.gif

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

Note: Although you are fetching one row at a time, Heterogeneous Services optimizes the round trips between the Oracle database server 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 parsing the SELECT statement, the rows are fetched and printed in a loop until the function FETCH_ROW returns the value 0.

## Optimizing Data Transfers Using Bulk Fetch

When an application fetches data from a non-Oracle system using Heterogeneous Services, data is transferred:

- From the non-Oracle system to the agent process

- From the agent process to the Oracle database server

- From the Oracle database server to the application

Oracle allows you to optimize all three data transfers, as illustrated in Figure 4-3.

#### Figure 4-3 Optimizing data transfers

Text description of the illustration heter003.gif

This section contains the following topics:

- Using OCI, an Oracle Precompiler, or Another Tool for Array Fetches

- Controlling the Array Fetch Between Oracle Database Server and Agent

- Controlling the Array Fetch Between Agent and Non-Oracle Server

- Controlling the Reblocking of Array Fetches

### Using OCI, an Oracle Precompiler, or Another Tool for Array Fetches

You can optimize data transfers between your application and the Oracle database server by using array fetches. See your application development tool documentation for information about array fetching and how to specify the amount of data to be transferred over the network.

### Controlling the Array Fetch Between Oracle Database Server and Agent

When Oracle retrieves data from a non-Oracle system, the Heterogeneous Services initialization parameter HS_RPC_FETCH_SIZE defines the number of bytes sent per fetch between the agent and the Oracle database server. The agent fetches data from the non-Oracle system until one of the following occurs:

- It has accumulated the specified number of bytes to send back to the Oracle database server

- The last row of the result set is fetched from the non-Oracle system

### Controlling the Array Fetch Between Agent and Non-Oracle Server

The initialization parameter HS_FDS_FETCH_ROWS determines the number of rows to be retrieved from a non-Oracle system. Note that the array fetch must be supported by the agent. See your agent-specific documentation to ensure that your agent supports array fetching.

### Controlling the Reblocking of Array Fetches

By default, an agent fetches data from the non-Oracle system until it has enough data retrieved to send back to the server. That is, it continues processing until the number of bytes fetched from the non-Oracle system is equal to or higher than the value of HS_RPC_FETCH_SIZE. In other words, the agent reblocks the data between the agent and the Oracle database server in sizes defined by the value of HS_RPC_FETCH_SIZE.

When the non-Oracle system supports array fetches, you can immediately send the data fetched from the non-Oracle system by the array fetch to the Oracle database server without waiting until the exact value of HS_RPC_FETCH_SIZE is reached. That is, you can stream the data from the non-Oracle system to the Oracle database server and disable reblocking by setting the value of initialization parameter HS_RPC_FETCH_REBLOCKING to OFF.

For example, assume that you set HS_RPC_FETCH_SIZE to 64K and HS_FDS_FETCH_ROWS to 100 rows. Assume that each row is approximately 600 bytes in size, so that the 100 rows are approximately 60K. When HS_RPC_FETCH_REBLOCKING is set to ON, the agent starts fetching 100 rows from the non-Oracle system.

Because there is only 60K bytes of data in the agent, the agent does not send the data back to the Oracle database server. Instead, the agent fetches the next 100 rows from the non-Oracle system. Because there is now 120K of data in the agent, the first 64K can be sent back to the Oracle database server.

Now there is 56K of data left in the agent. The agent fetches another 100 rows from the non-Oracle system before sending the next 64K of data to the Oracle database server. By setting the initialization parameter HS_RPC_FETCH_REBLOCKING to OFF, the first 100 rows are immediately sent back to the Oracle database server.

## Researching the Locking Behavior of Non-Oracle Systems

When designing applications with Heterogeneous Services, be aware that the Oracle database server and non-Oracle data sources can have different locking behaviors. For example, some non-Oracle data sources differ from the Oracle database server in how they set read and write locks on records in affected tables.

Oracle cannot change any aspect of the locking behavior of a non-Oracle data source. In order to avoid adverse effects on other users of the non-Oracle data source, all applications that access a non-Oracle data source must always adhere to the programming standards of that data source.

See Also: Your non-Oracle system's documentation for information about locking behavior.

## Limitations to Heterogeneous Services

Limitations to Heterogeneous Services include the following:

- Long columns are limited to 4 MB in size

- There is no support for the SQL*Plus DESCRIBE command

- There is no support for LOBs and object types

- Shared database links cannot be used with Heterogeneous Services

- There is no support for PL/SQL records

- There is no support for ref cursors

- Each specific gateway might have its own limitations

| Copyright © 1996-2001 Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index