The passthrough SQL feature enables you to send a statement directly to a non-Oracle system without first being interpreted by Oracle Database.
You can execute passthrough SQL statements directly on the non-Oracle system using the PL/SQL package
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.
Oracle Database PL/SQL Packages and Types Reference for more information about this package
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.
Executing Passthrough SQL Statements
This section describes the functions and procedures provided by the
DBMS_HS_PASSTHROUGH package that enable you to execute passthrough SQL statements.
Table 3-1 DBMS_HS_PASSTHROUGH Functions and Procedures
Opens a cursor.
Closes a cursor.
Parses the statement.
Executes a nonquery statement.
Executes a nonquery statement without bind variables.
Fetches rows from query.
Retrieves column value from
Nonqueries include the statements
DELETE and other DDL types of statements.
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;
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.
Overview of Bind Variables
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.
Open a cursor.
Parse the SQL statement on the non-Oracle system.
Bind the variables.
Execute the SQL statement on the non-Oracle system.
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
IN Bind Variables
The syntax of the non-Oracle system determines how a statement specifies a
IN bind variable.
... 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;
OUT Bind Variables
The non-Oracle system can support
OUT bind variables.
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.
IN OUT Bind Variables
A bind variable can be both an
IN and an
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.
Use passthrough SQL to execute queries.
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
You do not have to retrieve all the rows. You can close the cursor at any time after opening the cursor.
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;
SELECT statement has been parsed, the rows are fetched and printed in a loop until the
FETCH_ROW function returns the value