The pass-through SQL feature allows an application developer to send a statement directly to a non-Oracle system without being interpreted by the Oracle 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 run these statements directly at the non-Oracle system using the PL/SQL package DBMS_HS_PASSTHROUGH
. Any statement executed with this package is run in the same transaction as regular "transparent" SQL statements.
This chapter discusses the following topic:
Table 45-1 DBMS_HS_PASSTHROUGH Package Subprograms
Subprogram | Description |
---|---|
Binds |
|
Binds |
|
Binds an |
|
Binds an |
|
Binds an |
|
Binds |
|
Closes the cursor and releases associated memory after the SQL statement has been run at the non-Oracle system |
|
Runs a (non- |
|
Runs a (non- |
|
Fetches rows from a query |
|
Retrieves column value from |
|
Similar to |
|
Opens a cursor for running a passthrough SQL statement at the non-Oracle system |
|
Parses SQL statement at non-Oracle system |
This procedure binds IN
OUT
bind variables.
DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE ( c IN BINARY_INTEGER NOT NULL, p IN BINARY_INTEGER NOT NULL, v IN OUT <dty>, n IN VARCHAR2);
<dty>
is either DATE
, NUMBER
, or VARCHAR2
.
Table 45-2 BIND_INOUT_VARIABLE Procedure Parameters
Parameter | Description |
---|---|
|
Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed, using the routines |
|
Position of the bind variable in the SQL statement: Starts at 1. |
|
This value is used for two purposes: - To provide the IN value before the SQL statement is run. - To determine the size of the out value. |
|
(Optional) Name of the bind variable. For example, in |
Table 45-3 BIND_INOUT_VARIABLE Procedure Exceptions
Exception | Description |
---|---|
|
The cursor passed is invalid. |
|
Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?) |
|
The position of the bind variable is out of range. |
|
A |
Purity level defined : WNDS, RNDS
This procedure binds IN
OUT
bind variables of datatype RAW
.
DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE_RAW ( c IN BINARY_INTEGER NOT NULL, p IN BINARY_INTEGER NOT NULL, v IN OUT RAW, n IN VARCHAR2);
Table 45-4 BIND_INOUT_VARIABLE_RAW Procedure Parameters
Parameter | Description |
---|---|
|
Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed using the routines |
|
Position of the bind variable in the SQL statement: Starts at 1. |
|
This value is used for two purposes: - To provide the IN value before the SQL statement is run. - To determine the size of the out value. |
|
(Optional) Name the bind variable. For example, in |
Table 45-5 BIND_INOUT_VARIABLE_RAW Procedure Exceptions
Exception | Description |
---|---|
|
The cursor passed is invalid. |
|
Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?) |
|
The position of the bind variable is out of range. |
|
A |
Purity level defined : WNDS, RNDS
This procedure binds an OUT
variable with a PL/SQL program variable.
DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE ( c IN BINARY_INTEGER NOT NULL, p IN BINARY_INTEGER NULL, v OUT <dty>, n IN VARCHAR2);
<dty>
is either DATE
, NUMBER
, or VARCHAR2
.
Table 45-6 BIND_OUT_VARIABLE Procedure Parameters
Parameter | Description |
---|---|
|
Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed, using the routines |
|
Position of the bind variable in the SQL statement: Starts at 1. |
|
Variable in which the |
|
(Optional) Name of the bind variable. For example, in |
Table 45-7 BIND_OUT_VARIABLE Procedure Exceptions
Exception | Description |
---|---|
|
The cursor passed is invalid. |
|
Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?) |
|
The position of the bind variable is out of range. |
|
A |
Purity level defined : WNDS, RNDS
This procedure binds an OUT
variable of datatype RAW
with a PL/SQL program variable.
DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE_RAW ( c IN BINARY_INTEGER NOT NULL, p IN BINARY_INTEGER NOT NULL, v OUT RAW, n IN VARCHAR2);
Table 45-8 BIND_OUT_VARIABLE_RAW Procedure Parameters
Parameter | Description |
---|---|
|
Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed, using the routines |
|
Position of the bind variable in the SQL statement: Starts at 1. |
|
Variable in which the |
|
(Optional) Name of the bind variable. For example, in |
Table 45-9 BIND_OUT_VARIABLE_RAW Procedure Exceptions
Exception | Description |
---|---|
|
The cursor passed is invalid. |
|
Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?) |
|
The position of the bind variable is out of range. |
|
A |
Purity level defined : WNDS, RNDS
This procedure binds an IN
variable positionally with a PL/SQL program variable.
DBMS_HS_PASSTHROUGH.BIND_VARIABLE ( c IN BINARY_INTEGER NOT NULL, p IN BINARY_INTEGER NOT NULL, v IN <dty>, n IN VARCHAR2);
<dty>
is either DATE
, NUMBER
, or VARCHAR2
.
Table 45-10 BIND_VARIABLE Procedure Parameters
Parameter | Description |
---|---|
|
Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed using the routines |
|
Position of the bind variable in the SQL statement: Starts at 1. |
|
Value that must be passed to the bind variable name. |
|
(Optional) Name of the bind variable. For example, in |
Table 45-11 BIND_VARIABLE Procedure Exceptions
Exception | Description |
---|---|
|
The cursor passed is invalid. |
|
Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?) |
|
The position of the bind variable is out of range. |
|
A |
Purity level defined: WNDS, RNDS
This procedure binds IN
variables of type RAW
.
DBMS_HS_PASSTHROUGH.BIND_VARIABLE_RAW ( c IN BINARY_INTEGER NOT NULL, p IN BINARY_INTEGER NOT NULL, v IN RAW, n IN VARCHAR2);
Table 45-12 BIND_VARIABLE_RAW Procedure Parameters
Parameter | Description |
---|---|
|
Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed, using the routines |
|
Position of the bind variable in the SQL statement: Starts at 1. |
|
Value that must be passed to the bind variable. |
|
(Optional) Name of the bind variable. For example, in |
Table 45-13 BIND_VARIABLE_RAW Procedure Exceptions
Exception | Description |
---|---|
|
The cursor passed is invalid. |
|
Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?) |
|
The position of the bind variable is out of range. |
|
A |
Purity level defined : WNDS, RNDS
This function closes the cursor and releases associated memory after the SQL statement has been run at the non-Oracle system. If the cursor was not open, then the operation is a "no operation".
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR ( c IN BINARY_INTEGER NOT NULL);
Table 45-15 CLOSE_CURSOR Procedure Exceptions
Exception | Description |
---|---|
|
A |
Purity level defined : WNDS, RNDS
This function runs a SQL statement immediately. Any valid SQL command except SELECT
can be run immediately. The statement must not contain any bind variables. The statement is passed in as a VARCHAR2
in the argument. Internally the SQL statement is run using the PASSTHROUGH
SQL protocol sequence of OPEN_CURSOR
, PARSE
, EXECUTE_NON_QUERY
, CLOSE_CURSOR
.
DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE ( s IN VARCHAR2 NOT NULL) RETURN BINARY_INTEGER;
Table 45-16 EXECUTE_IMMEDIATE Procedure Parameters
Parameter | Description |
---|---|
|
|
The number of rows affected by the execution of the SQL statement.
Table 45-17 EXECUTE_IMMEDIATE Procedure Exceptions
Exception | Description |
---|---|
|
SQL statement is invalid. |
|
Max open cursors. |
|
A |
This function runs a SQL statement. The SQL statement cannot be a SELECT
statement. A cursor has to be open and the SQL statement has to be parsed before the SQL statement can be run.
DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY ( c IN BINARY_INTEGER NOT NULL) RETURN BINARY_INTEGER;
Table 45-18 EXECUTE_NON_QUERY Function Parameters
Parameter | Description |
---|---|
|
Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed, using the routines |
The number of rows affected by the SQL statement in the non-Oracle system
Table 45-19 EXECUTE_NON_QUERY Procedure Exceptions
Exception | Description |
---|---|
|
The cursor passed is invalid. |
|
|
|
A |
This function fetches rows from a result set. The result set is defined with a SQL SELECT
statement. When there are no more rows to be fetched, the exception NO_DATA_FOUND
is raised. Before the rows can be fetched, a cursor has to be opened, and the SQL statement has to be parsed.
DBMS_HS_PASSTHROUGH.FETCH_ROW ( c IN BINARY_INTEGER NOT NULL, f IN BOOLEAN) RETURN BINARY_INTEGER;
Table 45-20 FETCH_ROW Function Parameters
Parameter | Description |
---|---|
|
Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed, using the routines |
|
(Optional) Reexecutes
|
The returns the number of rows fetched. The function returns "0" if the last row was already fetched.
Table 45-21 FETCH_ROW Procedure Exceptions
Exception | Description |
---|---|
|
The cursor passed is invalid. |
|
Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?) |
|
A |
Purity level defined : WNDS
This procedure has two purposes:
It retrieves the select list items of SELECT
statements, after a row has been fetched.
It retrieves the OUT
bind values, after the SQL statement has been run.
DBMS_HS_PASSTHROUGH.GET_VALUE ( c IN BINARY_INTEGER NOT NULL, p IN BINARY_INTEGER NOT NULL, v OUT <dty>);
<dty>
is either DATE
, NUMBER
, or VARCHAR2
.
Table 45-22 GET_VALUE Procedure Parameters
Parameter | Description |
---|---|
|
Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed, using the routines |
|
Position of the bind variable or select list item in the SQL statement: Starts at 1. |
|
Variable in which the |
Table 45-23 GET_VALUE Procedure Exceptions
Exception | Description |
---|---|
|
Returns |
|
The cursor passed is invalid. |
|
Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?) |
|
The position of the bind variable is out of range. |
|
A |
Purity level defined : WNDS
This procedure is similar to GET_VALUE
, but for datatype RAW
.
DBMS_HS_PASSTHROUGH.GET_VALUE_RAW ( c IN BINARY_INTEGER NOT NULL, p IN BINARY_INTEGER NOT NULL, v OUT RAW);
Table 45-24 GET_VALUE_RAW Procedure Parameters
Parameter | Description |
---|---|
|
Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed, using the routines |
|
Position of the bind variable or select list item in the SQL statement: Starts at 1. |
|
Variable in which the |
Table 45-25 GET_VALUE_RAW Procedure Exceptions
Exception | Description |
---|---|
|
Returns |
|
The cursor passed is invalid. |
|
Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?) |
|
The position of the bind variable is out of range. |
|
A |
Purity level defined : WNDS
This function opens a cursor for running a pass-through SQL statement at the non-Oracle system. This function must be called for any type of SQL statement.
The function returns a cursor, which must be used in subsequent calls. This call allocates memory. To deallocate the associated memory, call the procedure CLOSE_CURSOR
.
DBMS_HS_PASSTHROUGH.OPEN_CURSOR RETURN BINARY_INTEGER;
The cursor to be used on subsequent procedure and function calls.
Table 45-26 OPEN_CURSOR Function Exceptions
Exception | Description |
---|---|
|
Maximum number of open cursor has been exceeded. Increase Heterogeneous Services' |
Purity level defined : WNDS, RNDS
This procedure parses SQL statement at non-Oracle system.
DBMS_HS_PASSTHROUGH.PARSE ( c IN BINARY_INTEGER NOT NULL, stmt IN VARCHAR2 NOT NULL);
Table 45-27 PARSE Procedure Parameters
Parameter | Description |
---|---|
|
Cursor associated with the pass-through SQL statement. Cursor must be opened using function |
|
Statement to be parsed. |
Table 45-28 PARSE Procedure Exceptions
Exception | Description |
---|---|
|
The cursor passed is invalid. |
|
SQL statement is illegal. |
|
A |
Purity level defined : WNDS, RNDS