14
DBMS_HS_PASSTHROUGH

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.

See Also:
For detailed information on Heterogeneous Services and on binding variables, see Oracle8i Distributed Database Systems.

Security

The DBMS_HS_PASSTHROUGH package conceptually resides at the non-Oracle system. Procedures and functions in the package must be called by using the appropriate database link to the non-Oracle system.

Summary of Subprograms

Table 14-1 DBMS_HS_PASSTHROUGH Package Subprograms

Subprogram	Description
BIND_VARIABLE procedure	Binds an `IN` variable positionally with a PL/SQL program variable.
BIND_VARIABLE_RAW procedure	Binds `IN` variables of type `RAW`.
BIND_OUT_VARIABLE procedure	Binds an `OUT` variable with a PL/SQL program variable.
BIND_OUT_VARIABLE_RAW procedure	Binds an `OUT` variable of datatype `RAW` with a PL/SQL program variable.
BIND_INOUT_VARIABLE procedure	Binds `IN` `OUT` bind variables.
BIND_INOUT_VARIABLE_RAW procedure	Binds `IN` `OUT` bind variables of datatype `RAW`.
CLOSE_CURSOR procedure	Closes the cursor and releases associated memory after the SQL statement has been run at the non-Oracle system.
EXECUTE_IMMEDIATE procedure	Runs a (non-`SELECT`) SQL statement immediately, without bind variables.
EXECUTE_NON_QUERY function	Runs a (non-`SELECT`) SQL statement.
FETCH_ROW function	Fetches rows from a query.
GET_VALUE procedure	Retrieves column value from `SELECT` statement, or retrieves `OUT` bind parameters.
GET_VALUE_RAW procedure	Similar to `GET_VALUE`, but for datatype `RAW`.
OPEN_CURSOR function	Opens a cursor for running a passthrough SQL statement at the non-Oracle system.
PARSE procedure	Parses SQL statement at non-Oracle system.

BIND_VARIABLE procedure

This procedure binds an IN variable positionally with a PL/SQL program variable.

Syntax

DBMS_HS_PASSTHROUGH.BIND_VARIABLE (
   c      IN BINARY_INTEGER NOT NULL,
   pos    IN BINARY_INTEGER NOT NULL,
   val    IN <dty>,
   name   IN VARCHAR2);

Where <dty> is either DATE, NUMBER, or VARCHAR2

See Also:
To bind RAW variables use BIND_VARIABLE_RAW procedure .

Parameters

Table 14-2 BIND_VARIABLE Procedure Parameters

Parameter	Description
c	Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed using the routines `OPEN_CURSOR` and `PARSE` respectively.
pos	Position of the bind variable in the SQL statement: Starts at 1.
val	Value that must be passed to the bind variable name.
name	(Optional) Name of the bind variable. For example, in `SELECT * FROM emp WHERE ename=:ename`, the position of the bind variable :`ename` is 1, the name is :`ename`. This parameter can be used if the non-Oracle system supports "named binds" instead of positional binds. Passing the position is still required.

Exceptions

Table 14-3 BIND_VARIABLE Procedure Exceptions

Exception	Description
ORA-28550	The cursor passed is invalid.
ORA-28552	Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?)
ORA-28553	The position of the bind variable is out of range.
ORA-28555	A `NULL` value was passed for a `NOT` `NULL` parameter.

Pragmas

Purity level defined: WNDS, RNDS

BIND_VARIABLE_RAW procedure

This procedure binds IN variables of type RAW.

Syntax

DBMS_HS_PASSTHROUGH.BIND_VARIABLE_RAW (
   c      IN BINARY_INTEGER NOT NULL,
   pos    IN BINARY_INTEGER NOT NULL,
   val    IN RAW,  
   name   IN VARCHAR2);

Parameters

Table 14-4 BIND_VARIABLE_RAW Procedure Parameters

Parameter	Description
c	Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed, using the routines `OPEN_CURSOR` and `PARSE` respectively.
pos	Position of the bind variable in the SQL statement: Starts at 1.
val	Value that must be passed to the bind variable.
name	(Optional) Name of the bind variable. For example, in `SELECT * FROM emp WHERE ename=:ename`, the position of the bind variable :`ename` is 1, the name is :`ename`. This parameter can be used if the non-Oracle system supports "named binds" instead of positional binds. Passing the position is still required.

Exceptions

Table 14-5 BIND_VARIABLE_RAW Procedure Exceptions

Exception	Description
ORA-28550	The cursor passed is invalid.
ORA-28552	Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?)
ORA-28553	The position of the bind variable is out of range.
ORA-28555	A `NULL` value was passed for a `NOT` `NULL` parameter.

Pragmas

Purity level defined : WNDS, RNDS

BIND_OUT_VARIABLE procedure

This procedure binds an OUT variable with a PL/SQL program variable.

Syntax

DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE (
   c        IN  BINARY_INTEGER NOT NULL,
   pos      IN  BINARY_INTEGER NOT NULL,
   val      OUT <dty>,
   name     IN  VARCHAR2);

Where <dty> is either DATE, NUMBER, or VARCHAR2

See Also:
For binding OUT variables of datatype RAW, see BIND_OUT_VARIABLE_RAW procedure .

Parameters

Table 14-6 BIND_OUT_VARIABLE Procedure Parameters

Parameter	Description
c	Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed, using the routines `OPEN_CURSOR` and `PARSE` respectively.
pos	Position of the bind variable in the SQL statement: Starts at 1.
val	Variable in which the `OUT` bind variable stores its value. The package remembers only the "size" of the variable. After the SQL statement is run, you can use `GET_VALUE` to retrieve the value of the `OUT` parameter. The size of the retrieved value should not exceed the size of the parameter that was passed using `BIND_OUT_VARIABLE`.
name	(Optional) Name of the bind variable. For example, in `SELECT * FROM emp WHERE ename=:ename`, the position of the bind variable :`ename` is 1, the name is :`ename`. This parameter can be used if the non-Oracle system supports "named binds" instead of positional binds. Passing the position is still required.

Exceptions

Table 14-7 BIND_OUT_VARIABLE Procedure Exceptions

Exception	Description
ORA-28550	The cursor passed is invalid.
ORA-28552	Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?)
ORA-28553	The position of the bind variable is out of range.
ORA-28555	A `NULL` value was passed for a `NOT` `NULL` parameter.

Pragmas

Purity level defined : WNDS, RNDS

BIND_OUT_VARIABLE_RAW procedure

This procedure binds an OUT variable of datatype RAW with a PL/SQL program variable.

Syntax

DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE (
   c       IN  BINARY_INTEGER NOT NULL,
   pos     IN  BINARY_INTEGER NOT NULL,
   val     OUT RAW,
   name    IN  VARCHAR2);

Parameters

Table 14-8 BIND_OUT_VARIABLE_RAW Procedure Parameters

Parameter	Description
c	Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed, using the routines `OPEN_CURSOR` and `PARSE` respectively.
pos	Position of the bind variable in the SQL statement: Starts at 1.
val	Variable in which the `OUT` bind variable stores its value. The package remembers only the "size" of the variable. After the SQL statement is run, you can use `GET_VALUE` to retrieve the value of the `OUT` parameter. The size of the retrieved value should not exceed the size of the parameter that was passed using `BIND_OUT_VARIABLE_RAW`.
name	(Optional) Name of the bind variable. For example, in `SELECT * FROM emp WHERE ename=:ename`, the position of the bind variable :`ename` is 1, the name is :`ename`. This parameter can be used if the non-Oracle system supports "named binds" instead of positional binds. Passing the position is still required.

Exceptions

Table 14-9 BIND_OUT_VARIABLE_RAW Procedure Exceptions

Exception	Description
ORA-28550	The cursor passed is invalid.
ORA-28552	Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?)
ORA-28553	The position of the bind variable is out of range.
ORA-28555	A `NULL` value was passed for a `NOT` `NULL` parameter.

Pragmas

Purity level defined : WNDS, RNDS

BIND_INOUT_VARIABLE procedure

This procedure binds IN OUT bind variables.

Syntax

DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE (
   c       IN     BINARY_INTEGER NOT NULL,
   pos     IN     BINARY_INTEGER NOT NULL,
   val     IN OUT <dty>,
   name    IN     VARCHAR2);

Where <dty> is either DATE, NUMBER, or VARCHAR2

See Also:
For binding IN OUT variables of datatype RAW see BIND_INOUT_VARIABLE_RAW procedure .

Parameters

Table 14-10 BIND_INOUT_VARIABLE Procedure Parameters

Parameter	Description
c	Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed, using the routines `OPEN_CURSOR` and `PARSE` respectively.
pos	Position of the bind variable in the SQL statement: Starts at 1.
val	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.
name	(Optional) Name of the bind variable. For example, in `SELECT * FROM emp WHERE ename=:ename`, the position of the bind variable :`ename` is 1, the name is :`ename`. This parameter can be used if the non-Oracle system supports "named binds" instead of positional binds. Passing the position is still required.

Exceptions

Table 14-11 BIND_INOUT_VARIABLE Procedure Exceptions

Exception	Description
ORA-28550	The cursor passed is invalid.
ORA-28552	Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?)
ORA-28553	The position of the bind variable is out of range.
ORA-28555	A `NULL` value was passed for a `NOT` `NULL` parameter.

Pragmas

Purity level defined : WNDS, RNDS

BIND_INOUT_VARIABLE_RAW procedure

This procedure binds IN OUT bind variables of datatype RAW.

Syntax

DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE (
   c        IN     BINARY_INTEGER NOT NULL,
   pos      IN     BINARY_INTEGER NOT NULL,
   val      IN OUT RAW,
   name     IN     VARCHAR2);

Parameters

Table 14-12 BIND_INOUT_VARIABLE_RAW Procedure Parameters

Parameter	Description
c	Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed' using the routines `OPEN_CURSOR` and `PARSE` respectively.
pos	Position of the bind variable in the SQL statement: Starts at 1.
val	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.
name	(Optional) Name the bind variable. For example, in `SELECT * FROM emp WHERE ename=:ename`, the position of the bind variable :`ename` is 1, the name is :`ename`. This parameter can be used if the non-Oracle system supports "named binds" instead of positional binds. Passing the position is still required.

Exceptions

Table 14-13 BIND_INOUT_VARIABLE_RAW Procedure Exceptions

Exception	Description
ORA-28550	The cursor passed is invalid.
ORA-28552	Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?)
ORA-28553	The position of the bind variable is out of range.
ORA-28555	A `NULL` value was passed for a `NOT` `NULL` parameter.

Pragmas

Purity level defined : WNDS, RNDS

CLOSE_CURSOR procedure

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".

Syntax

DBMS_HS_PASSTHROUGH.CLOSE_CURSOR (
   c IN BINARY_INTEGER NOT NULL);

Parameters

Table 14-14 CLOSE_CURSOR Procedure Parameters

Parameter	Description
c	Cursor to be released.

Exceptions

Table 14-15 CLOSE_CURSOR Procedure Exceptions

Exception	Description
ORA-28555	A `NULL` value was passed for a `NOT` `NULL` parameter.

Pragmas

Purity level defined : WNDS, RNDS

EXECUTE_IMMEDIATE procedure

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.

Syntax

DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE ( 
   S IN VARCHAR2 NOT NULL)  
RETURN BINARY_INTEGER;

Parameters

Table 14-16 EXECUTE_IMMEDIATE Procedure Parameters

Parameter	Description
s	`VARCHAR2` variable with the statement to be executed immediately.

Returns

The number of rows affected by the execution of the SQL statement.

Exceptions

Table 14-17 EXECUTE_IMMEDIATE Procedure Exceptions

Exception	Description
ORA-28551	SQL statement is invalid.
ORA-28544	Max open cursors.
ORA-28555	A `NULL` value was passed for a `NOT` `NULL` parameter.

Pragmas

None

EXECUTE_NON_QUERY function

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.

Syntax

DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY ( 
   c IN BINARY_INTEGER NOT NULL)
  RETURN BINARY_INTEGER;

Parameters

Table 14-18 EXECUTE_NON_QUERY Function Parameters

Parameter	Description
c	Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed, using the routines `OPEN_CURSOR` and `PARSE` respectively.

Returns

The number of rows affected by the SQL statement in the non-Oracle system

Exceptions

Table 14-19 EXECUTE_NON_QUERY Procedure Exceptions

Exception	Description
ORA-28550	The cursor passed is invalid.
ORA-28552	`BIND_VARIABLE` procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?)
ORA-28555	A `NULL` value was passed for a `NOT` `NULL` parameter.

Pragmas

None

FETCH_ROW function

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.

Syntax

DBMS_HS_PASSTHROUGH.FETCH_ROW (
   c       IN BINARY_INTEGER NOT NULL,
   first   IN BOOLEAN)
  RETURN BINARY_INTEGER;

Parameters

Table 14-20 FETCH_ROW Function Parameters

Parameter Description

Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed, using the routines OPEN_CURSOR and PARSE respectively.

first

(Optional) Reexecutes SELECT statement. Possible values:

- TRUE: reexecute SELECT statement.

- FALSE: fetch the next row, or if run for the first time, then execute and fetch rows (default).

Returns

The returns the number of rows fetched. The function returns "0" if the last row was already fetched.

Exceptions

Table 14-21 FETCH_ROW Procedure Exceptions

Exception	Description
ORA-28550	The cursor passed is invalid.
ORA-28552	Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?)
ORA-28555	A `NULL` value was passed for a `NOT` `NULL` parameter.

Pragmas

Purity level defined : WNDS

GET_VALUE procedure

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.

Syntax

DBMS_HS_PASSTHROUGH.GET_VALUE (
   c      IN  BINARY_INTEGER NOT NULL,
   pos    IN  BINARY_INTEGER NOT NULL,
   val    OUT <dty>);

Where <dty> is either DATE, NUMBER, or VARCHAR2

See Also:
For retrieving values of datatype RAW, see GET_VALUE_RAW procedure .

Parameters

Table 14-22 GET_VALUE Procedure Parameters

Parameter	Description
c	Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed, using the routines `OPEN_CURSOR` and `PARSE` respectively.
pos	Position of the bind variable or select list item in the SQL statement: Starts at 1.
val	Variable in which the `OUT` bind variable or select list item stores its value.

Exceptions

Table 14-23 GET_VALUE Procedure Exceptions

Exception	Description
ORA-1403	Returns `NO_DATA_FOUND` exception when running the `GET_VALUE` after the last row was fetched (i.e., `FETCH_ROW` returned "0").
ORA-28550	The cursor passed is invalid.
ORA-28552	Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?)
ORA-28553	The position of the bind variable is out of range.
ORA-28555	A `NULL` value was passed for a `NOT` `NULL` parameter.

Pragmas

Purity level defined : WNDS

GET_VALUE_RAW procedure

This procedure is similar to GET_VALUE, but for datatype RAW.

Syntax

DBMS_HS_PASSTHROUGH.GET_VALUE_RAW (
   c      IN  BINARY_INTEGER NOT NULL,
   pos    IN  BINARY_INTEGER NOT NULL,
   val    OUT RAW);

Parameters

Table 14-24 GET_VALUE_RAW Procedure Parameters

Parameter	Description
c	Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed, using the routines `OPEN_CURSOR` and `PARSE` respectively.
pos	Position of the bind variable or select list item in the SQL statement: Starts at 1.
val	Variable in which the `OUT` bind variable or select list item stores its value.

Exceptions

Table 14-25 GET_VALUE_RAW Procedure Exceptions

Exception	Description
ORA-1403	Returns `NO_DATA_FOUND` exception when running the `GET_VALUE` after the last row was fetched (i.e., `FETCH_ROW` returned "0").
ORA-28550	The cursor passed is invalid.
ORA-28552	Procedure is not run in right order. (Did you first open the cursor and parse the SQL statement?)
ORA-28553	The position of the bind variable is out of range.
ORA-28555	A `NULL` value was passed for a `NOT` `NULL` parameter.

Pragmas

Purity level defined : WNDS

OPEN_CURSOR function

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.

Syntax

DBMS_HS_PASSTHROUGH.OPEN_CURSOR 
  RETURN BINARY_INTEGER;

Returns

The cursor to be used on subsequent procedure and function calls.

Exceptions

Table 14-26 OPEN_CURSOR Function Exceptions

Exception	Description
ORA-28554	Maximum number of open cursor has been exceeded. Increase Heterogeneous Services' `OPEN_CURSORS` initialization parameter.

Pragmas

Purity level defined : WNDS, RNDS

PARSE procedure

This procedure parses SQL statement at non-Oracle system.

Syntax

DBMS_HS_PASSTHROUGH.GET_VALUE_RAW (
   c       IN  BINARY_INTEGER NOT NULL,
   stmt    IN  VARCHAR2       NOT NULL);

Parameters

Table 14-27 PARSE Procedure Parameters

Parameter	Description
c	Cursor associated with the pass-through SQL statement. Cursor must be opened using function `OPEN_CURSOR`.
stmt	Statement to be parsed.

Exceptions

Table 14-28 GET_VALUE Procedure Exceptions

Exception	Description
ORA-28550	The cursor passed is invalid.
ORA-28551	SQL statement is illegal.
ORA-28555	A `NULL` value was passed for a `NOT` `NULL` parameter.

Pragmas

Purity level defined : WNDS, RNDS

14DBMS_HS_PASSTHROUGH

Security

Summary of Subprograms

Table 14-1 DBMS_HS_PASSTHROUGH Package Subprograms

BIND_VARIABLE procedure

Syntax

Parameters

Table 14-2 BIND_VARIABLE Procedure Parameters

Exceptions

Table 14-3 BIND_VARIABLE Procedure Exceptions

Pragmas

BIND_VARIABLE_RAW procedure

Syntax

Parameters

Table 14-4 BIND_VARIABLE_RAW Procedure Parameters

Exceptions

Table 14-5 BIND_VARIABLE_RAW Procedure Exceptions

Pragmas

BIND_OUT_VARIABLE procedure

Syntax

Parameters

Table 14-6 BIND_OUT_VARIABLE Procedure Parameters

Exceptions

Table 14-7 BIND_OUT_VARIABLE Procedure Exceptions

Pragmas

BIND_OUT_VARIABLE_RAW procedure

Syntax

Parameters

Table 14-8 BIND_OUT_VARIABLE_RAW Procedure Parameters

Exceptions

Table 14-9 BIND_OUT_VARIABLE_RAW Procedure Exceptions

Pragmas

BIND_INOUT_VARIABLE procedure

Syntax

Parameters

Table 14-10 BIND_INOUT_VARIABLE Procedure Parameters

Exceptions

Table 14-11 BIND_INOUT_VARIABLE Procedure Exceptions

Pragmas

BIND_INOUT_VARIABLE_RAW procedure

Syntax

Parameters

Table 14-12 BIND_INOUT_VARIABLE_RAW Procedure Parameters

Exceptions

Table 14-13 BIND_INOUT_VARIABLE_RAW Procedure Exceptions

Pragmas

CLOSE_CURSOR procedure

Syntax

Parameters

Table 14-14 CLOSE_CURSOR Procedure Parameters

Exceptions

Table 14-15 CLOSE_CURSOR Procedure Exceptions

Pragmas

EXECUTE_IMMEDIATE procedure

Syntax

Parameters

Table 14-16 EXECUTE_IMMEDIATE Procedure Parameters

Returns

Exceptions

Table 14-17 EXECUTE_IMMEDIATE Procedure Exceptions

Pragmas

EXECUTE_NON_QUERY function

Syntax

Parameters

Table 14-18 EXECUTE_NON_QUERY Function Parameters

Returns

Exceptions

Table 14-19 EXECUTE_NON_QUERY Procedure Exceptions

Pragmas

FETCH_ROW function

Syntax

Parameters

Table 14-20 FETCH_ROW Function Parameters

Returns

Exceptions

Table 14-21 FETCH_ROW Procedure Exceptions

Pragmas

GET_VALUE procedure

Syntax

Parameters

14
DBMS_HS_PASSTHROUGH