Release 8.0
A54653_01
 Library |
 Product |
 Contents |
 Index |
| Oracle8
Server Distributed Database Systems Release 8.0 A54653_01 |
|
This chapter provides information for application developers who want to use Heterogeneous Services.
Topics covered include:
When writing applications, you need not be concerned that a non-Oracle system is accessed. Heterogeneous Services makes the non-Oracle system appear as if it were another Oracle8 Server.
However, on occasion, you might need to access a non-Oracle system using that non-Oracle system's SQL dialect. To make this possible, Heterogeneous Services provides a pass-through SQL feature that allows application programmers to directly execute the native SQL statement directly 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 Server. This chapter also discusses how to tune such data transfers.
The pass-through SQL feature allows an application developer to send a statement directly to a non-Oracle system without being interpreted by the Oracle8 Server. This can be useful if the non-Oracle system allows for operations in statements for which there is no equivalent in Oracle. 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 regular "transparent" SQL statements.
The DBMS_HS_PASSTHROUGH package conceptually resides at the non-Oracle system. Procedures and functions in the package must be invoked by using the appropriate database link to the non-Oracle system.
There are transaction implications when you execute a pass-through SQL statement that (implicitly) commit or rolls back the transaction in the non-Oracle system. For example, some systems implicitly commit the transaction when a Data Definition Language (DDL) statement is executed. Since the Oracle server is bypassed, the Oracle server is not aware of the commit in the non-Oracle system. This means that the data at the non-Oracle system can be committed while the transaction in the Oracle server is not.
If the transaction in Oracle server is rolled back, data inconsistencies between the Oracle server and the non-Oracle server can occur (i.e. global data inconsistency).
Note that if the application executes a regular COMMIT, the Oracle Server can coordinate the distributed transaction with the non-Oracle system. The pass-through SQL statement will be part of the distributed transaction, which can then be atomically committed or rolled back
Table 8-1 shows the functions and procedures provided by the DBMS_HS_PASSTHROUGH package that allow you to execute pass-through SQL statements. The following sections describe how to use them. The statements fall into two classes:
To execute non-query statements, you 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", you 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 will be returned.
You cannot execute a statement with EXECUTE_IMMEDIATE as a query (i.e. a SELECT statement) and you cannot use bind variables.
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:
Figure 8-1 shows the flow diagram for executing nonqueries with bind variables.
Figure 8-1: Flow Diagram for Nonquery Pass-Through SQL
How a bind variable is specified in a statement is determined by syntax of the non-Oracle system. 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 above statement, you can use the following
PL/SQL program:
DECLARE
c 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');
DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@SalesDB(c);
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@salesDB(c);
END;
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 SQL statement is executed.
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.
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.
The difference between queries and nonqueries is that queries retrieve a result set. The result set is retrieved by iterating over a cursor. After the SELECT statement is parsed, 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, indicated by a NO_DATA_FOUND exception, you can close the cursor. See Figure 8-2.
Figure 8-2: Pass-through SQL for Queries
It is not necessary to fetch all the rows. You can close the cursor at any time after opening the cursor, for example, after fetching a few rows.
You can open a new cursor and execute further pass-through SQL statements.
Alternatively, you can reuse the cursor for this SQL statement and simply rebind the bind variable with a new value without reopening the cursor and without re-parsing the SQL statement at the non-Oracle system.
Note: Although you are fetching one row at a time, Heterogeneous Services optimizes the round trips between the Oracle8 Server and the non-Oracle system by buffering multiple rows, and fetching from the non-Oracle data system in one round trip.
The next 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);
DBMS_HS_PASSTHROUGH.GET_VALUE@SalesDB(c, 1, val);
DBMS_OUTPUT.PUT_LINE(val);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@SalesDB(c);
END;
After parsing the SELECT statement, the rows are fetched and printed in a loop, until the exception NO_DATA_FOUND is raised. In the exception, the cursor is closed.
When an application fetches data from a non-Oracle system, using Heterogeneous Services, data is transferred
Oracle allows you optimize all three data transfers. See Figure 8-3.
Figure 8-3: Optimizing data transfers
You can optimize data transfers between your application and the Oracle8 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 sent per network round trip.
When data is retrieved from a non-Oracle system, the Heterogeneous Services initialization parameter HS_RPC_FETCH_SIZE defines the number of bytes that will be sent between the agent and the Oracle8 server. The agent will fetch data from the non-Oracle system until it has accumulated the specified number of bytes to sent back to the Oracle server or when the last row of the result set is fetched from the non-Oracle system.
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 your agent supports array fetching.
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, when the number of bytes fetched from the non-Oracle system equals the value of HS_RPC_FETCH_SIZE. In other words, the agent will "reblock" the data between the agent and the Oracle server in sizes defined by the value of HS_RPC_FETCH_SIZE.
When the non-Oracle system supports array fetches, you might want to immediately send the data fetched from the non-Oracle system by the array fetch to the Oracle server, without waiting until the exact value of HS_RPC_FETCH_SIZE is reached. That is, you want to stream the data from the non-Oracle system to the Oracle server, and disable reblocking. You can do this by setting the value of initialization parameter HS_RPC_FETCH_REBLOCKING to 'off'.
For example, you set HS_RPC_FETCH_SIZE to 64Kbytes and HS_FDS_FETCH_ROWS to 100 rows. Assume each row is approximately 600 bytes in size, and thus 100 rows is approximately 60Kbytes. When HS_RPC_FETCH_REBLOCKING is set to 'on' (i.e. the default), the agent would start fetching 100 rows from the non-Oracle system. Since there is only 60K bytes of data in the agent, the agent will not sent the data back to the Oracle server. Instead, the agent fetches the next 100 rows from the non-Oracle system. Since there is now 120Kbytes of data in the agent, the first 64Kbytes can be sent back to the Oracle Server. Now there is 56Kbytes left in the agent. The agent will fetch another 100 rows from the non-Oracle system, before sending the next 64Kbytes of data to the Oracle server.
You can choose to not reblock. By setting the initialization parameter HS_RPC_FETCH_REBLOCKING to 'off', the agent will not buffer the data to accumulate "enough" data. Instead, the first 100 rows will immediately be sent back to the Oracle8 Server. That is, the data is streamed from the non-Oracle system directly to the Oracle Server.
|
 Copyright © 1997 Oracle Corporation. All Rights Reserved. |
|