C
DBMS_HS_PASSTHROUGH for Pass-Through SQL

The package DBMS_HS_PASSTHROUGH contains the procedures and functions for pass-through SQL of Heterogeneous Services.

This appendix contains these topics:

• BIND_VARIABLE procedure

• BIND_VARIABLE_RAW procedure

• BIND_OUT_VARIABLE procedure

• BIND_OUT_VARIABLE_RAW procedure

• BIND_INOUT_VARIABLE procedure

• BIND_INOUT_VARIABLE_RAW procedure

• CLOSE_CURSOR function

• EXECUTE_IMMEDIATE function

• EXECUTE_NON_QUERY function

• FETCH_ROW function

• GET_VALUE procedure

• GET_VALUE_RAW procedure

• OPEN_CURSOR function

• PARSE procedure

  See Also: Chapter 4, "Developing Applications with Heterogeneous Services" for more information about this package.

Summary of Subprograms

Table C-1 DBMS_HS 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 function	Closes the cursor and releases associated memory after the SQL statement has been executed at the non-Oracle system
EXECUTE_IMMEDIATE function	Executes a SQL statement immediately
EXECUTE_NON_QUERY function	Executes any SQL statement other than a SELECT statement
FETCH_ROW function	Fetches rows from a result set
GET_VALUE procedure	Retrieves the select list items of SELECT statements after a row has been fetched, and retrieves the OUT bind values after the SQL statement has been executed
GET_VALUE_RAW procedure	Retrieves the select list items of SELECT statements after a row has been fetched, and retrieves the OUT bind values after the SQL statement has been executed. This procedure operates on the RAW datatype
OPEN_CURSOR function	Opens a cursor for executing a pass-through SQL statement at the non-Oracle system
PARSE procedure	Parses a SQL statement at non-Oracle system

BIND_VARIABLE procedure

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

See Also: Chapter 4, "Developing Applications with Heterogeneous Services".

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 one of

• DATE

• NUMBER

• VARCHAR2

  See Also: BIND_VARIABLE_RAW procedure

Parameters

Table C-2 BIND_VARIABLE Procedure Parameters

Parameter	Description
c	Cursor associated with the pass-through SQL statement. The cursor must be opened and parsed using the routines OPEN_CURSOR and PARSE.
pos	Position of the bind variable in the SQL statement. Starts from 1
val	Value that must be passed to the bind variable
name	Optional parameter to name the bind variable. For example, consider the following statement: SELECT * FROM emp WHERE ename=:ename; The position of the bind variable :ename is 1 and the name is :ename. You can use this parameter if the non-Oracle system supports named binds instead of positional binds. Note that passing the position is still required.

Exceptions

Table C-3 BIND_VARIABLE Procedure Exceptions

Exception	Description
ORA-28550	The cursor passed is invalid
ORA-28552	The procedure is not executed 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 levels defined: WNDS, RNDS

See Also: OPEN_CURSOR function PARSE procedure BIND_OUT_VARIABLE procedure BIND_VARIABLE_RAW procedure

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 C-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.
pos	Position of the bind variable in the SQL statement. Starts from 1.
val	Value that must be passed to the bind variable
name	Optional parameter to name the bind variable. For example, consider the following statement: SELECT * FROM emp WHERE ename=:ename; The position of the bind variable :ename is 1 and the name is :ename. You can use this parameter if the non-Oracle system supports named binds instead of positional binds. Note that passing the position is still required.

Exceptions

Table C-5 BIND_VARIABLE_RAW Procedure Exceptions

Exception	Description
ORA-28550	The cursor passed is invalid
ORA-28552	Procedure is not executed 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

See Also: OPEN_CURSOR function PARSE procedure BIND_VARIABLE procedure BIND_OUT_VARIABLE procedure

BIND_OUT_VARIABLE procedure

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

See Also: See Chapter 4, "Developing Applications with Heterogeneous Services" for more information about binding OUT parameters.

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 one of

• DATE

• NUMBER

• VARCHAR2

See Also: BIND_OUT_VARIABLE_RAW procedure for more information about OUT variables of datatype RAW.

Parameters

Table C-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 from 1.
val	Variable in which the OUT bind variable will store its value. The package will remember only the size of the variable. After the SQL statement is executed, 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 parameter to name the bind variable. For example, consider the following statement: SELECT * FROM emp WHERE ename=:ename; The position of the bind variable :ename is 1 and the name is :ename. You can use this parameter if the non-Oracle system supports named binds instead of positional binds. Note that passing the position is still required.

Exceptions

Table C-7 BIND_OUT_VARIABLE Procedure Exceptions

Exception	Description
ORA-28550	The cursor passed is invalid.
ORA-28552	Procedure is not executed 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

See Also: OPEN_CURSOR function PARSE procedure BIND_OUT_VARIABLE_RAW procedure BIND_VARIABLE procedure BIND_VARIABLE_RAW procedure GET_VALUE procedure

BIND_OUT_VARIABLE_RAW procedure

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

See Also: Chapter 4, "Developing Applications with Heterogeneous Services" for more information on binding OUT parameters.

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 C-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 from 1.
val	Variable in which the OUT bind variable will store its value. The package will remember only the size of the variable. After the SQL statement is executed, 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 parameter to name the bind variable. For example, consider the following statement: SELECT * FROM emp WHERE ename=:ename; The position of the bind variable :ename is 1 and the name is :ename. You can use this parameter if the non-Oracle system supports named binds instead of positional binds. Note that passing the position is still required.

Exceptions

Table C-9 BIND_OUT_VARIABLE_RAW Parameter Exceptions

Exception	Description
ORA-28550	The cursor passed is invalid.
ORA-28552	Procedure is not executed 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

Pragmas defined: WNDS, RNDS

See Also: OPEN_CURSOR function PARSE procedure BIND_OUT_VARIABLE procedure BIND_VARIABLE procedure BIND_VARIABLE_RAW procedure GET_VALUE procedure

BIND_INOUT_VARIABLE procedure

This procedure binds IN OUT bind variables.

See Also: Chapter 4, "Developing Applications with Heterogeneous Services" for more information on binding IN OUT parameters.

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 one of

• DATE

• NUMBER

• VARCHAR2

For binding IN OUT variables of datatype RAW, see BIND_INOUT_VARIABLE_RAW.

Parameters

Table C-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 from 1.
val	This value will be used for two purposes: To provide the IN value before the SQL statement is executed To determine the size of the OUT value
name	Optional parameter to name the bind variable. For example, consider the following statement: SELECT * FROM emp WHERE ename=:ename; The position of the bind variable :ename is 1 and the name is :ename. You can use this parameter if the non-Oracle system supports named binds instead of positional binds. Note that passing the position is still required.

Exceptions

Table C-11 BIND_INOUT_VARIABLE Procedure Exceptions

Exception	Description
ORA-28550	The cursor passed is invalid.
ORA-28552	Procedure is not executed 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

See Also: OPEN_CURSOR function PARSE procedure BIND_INOUT_VARIABLE_RAW procedure BIND_OUT_VARIABLE procedure BIND_OUT_VARIABLE_RAW procedure BIND_VARIABLE procedure BIND_VARIABLE_RAW procedure GET_VALUE procedure

BIND_INOUT_VARIABLE_RAW procedure

This procedure binds IN OUT bind variables of datatype RAW.

See Also : Chapter 4, "Developing Applications with Heterogeneous Services" for more information on binding IN OUT parameters.

See 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 C-12 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 from 1.
val	This value will be used for two purposes: To provide the IN value before the SQL statement is executed To determine the size of the out value
name	Optional parameter to name the bind variable. For example, consider the following statement: SELECT * FROM emp WHERE ename=:ename; The position of the bind variable :ename is 1 and the name is :ename. You can use this parameter if the non-Oracle system supports named binds instead of positional binds. Note that passing the position is still required.

Exceptions

Table C-13 BIND_INOUT_VARIABLE Procedure Exceptions

Exception	Description
ORA-28550	The cursor passed is invalid.
ORA-28552	Procedure is not executed 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

Pragmas defined: WNDS, RNDS

See Also: OPEN_CURSOR function PARSE procedure BIND_INOUT_VARIABLE procedure BIND_OUT_VARIABLE procedure BIND_OUT_VARIABLE_RAW procedure BIND_VARIABLE procedure BIND_VARIABLE_RAW procedure GET_VALUE procedure

CLOSE_CURSOR function

This function closes the cursor and releases associated memory after the SQL statement has been executed at the non-Oracle system. If the cursor was not open, the operation is a no operation.

Syntax

DBMS_HS_PASSTHROUGH.CLOSE_CURSOR (
     c   IN   BINARY_INTEGER NOT NULL);

Parameter

Table C-14 CLOSE_CURSOR Procedure Parameters

Parameter	Description
c	Cursor to be released.

Exceptions

Table C-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

See Also: OPEN_CURSOR function

EXECUTE_IMMEDIATE function

This function executes a SQL statement immediately. Any valid SQL command except SELECT can be executed immediately, but the statement must not contain any bind variables. The statement is passed in as a VARCHAR2 in the argument. Internally, the SQL statement is executed using the PASSTHROUGH SQL protocol sequence of OPEN_CURSOR, PARSE, EXECUTE_NON_QUERY, CLOSE_CURSOR.

Syntax

EXECUTE_IMMEDIATE ( s IN VARCHAR2 NOT NULL )
RETURN BINARY_INTEGER;

Parameter Description

Table C-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 C-17 EXECUTE_IMMEDIATE Procedure Exceptions

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

Pragmas

Purity level defined: NONE

See Also: OPEN_CURSOR function PARSE procedure EXECUTE_NON_QUERY function CLOSE_CURSOR function

EXECUTE_NON_QUERY function

This function executes any SQL statement other than a SELECT statement. A cursor has to be open and the SQL statement has to be parsed before the SQL statement can be executed.

Syntax

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

Parameter

Table C-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

s

Table C-19 EXECUTE_NON_QUERY Function Exceptions

Exception	Description
ORA-28550	The cursor passed is invalid.
ORA-28552	BIND_VARIABLE procedure is not executed 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: NONE

See Also: OPEN_CURSOR function PARSE procedure

FETCH_ROW function

This function fetches rows from a result set. The result set is defined with a SQL SELECT statement.

Before the rows can be fetched, a cursor has to be opened, and the SQL statement has to be parsed. When there are no more rows to be fetched, the function returns 0. After a 0 return, the NO_DATA_FOUND exception occurs when:

• A subsequent FETCH_ROW is attempted

• A GET_VALUE is attempted

Syntax

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

Parameters and Descriptions

Table C-20 FETCH_ROW 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.
first	Optional parameter to re-execute a SELECT statement. Possible values: TRUE: re-execute SELECT statement. FALSE: fetch the next row, or if executed for the first time execute and fetch rows (default).

Returns

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

Exceptions

Table C-21 FETCH_ROW Function Exceptions

Exception	Description
ORA-28550	The cursor passed is invalid.
ORA-28552	Procedure is not executed 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

See Also: OPEN_CURSOR function PARSE procedure

GET_VALUE procedure

This procedure has two purposes:

• To retrieve the select list items of SELECT statements after a row has been fetched.

• To retrieve the OUT bind values after the SQL statement has been executed.

Syntax

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

Where dty is one of

• DATE

• NUMBER

• VARCHAR2

For retrieving values of datatype RAW, see GET_VALUE_RAW.

Parameters

Table C-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 from 1.
val	Variable in which the OUT bind variable or select list item will store its value.

Exceptions

Table C-23 GET_RAW Procedure Exceptions

Exception	Description
ORA-1403	Returns NO_DATA_FOUND exception when executing 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 executed in right order. Did you first open the cursor, parse and execute (or fetch) 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

See Also: OPEN_CURSOR function PARSE procedure FETCH_ROW function GET_VALUE_RAW procedure BIND_OUT_VARIABLE procedure BIND_OUT_VARIABLE_RAW procedure BIND_INOUT_VARIABLE procedure BIND_INOUT_VARIABLE_RAW procedure

GET_VALUE_RAW procedure

This procedure, which operates on RAW datatypes, has two purposes:

• To retrieve the select list items of SELECT statements after a row has been fetched.

• To retrieve the OUT bind values after the SQL statement has been executed.

Syntax

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

Parameters

Table C-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 from 1.
val	Variable in which the OUT bind variable or select list item will store its value.

Exceptions

Table C-25 GET_VALUE_RAW Procedure Exceptions

Exception	Description
ORA-1403	Returns NO_DATA_FOUND exception when executing 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 executed in right order. Did you first open the cursor, parse and execute (or fetch) 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

See Also: OPEN_CURSOR function PARSE procedure FETCH_ROW function GET_VALUE procedure BIND_OUT_VARIABLE procedure BIND_OUT_VARIABLE_RAW procedure BIND_INOUT_VARIABLE procedure BIND_INOUT_VARIABLE_RAW procedure

OPEN_CURSOR function

This function opens a cursor for executing 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, you call the procedure DBMS_HS_PASSTHROUGH.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 C-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

See Also: CLOSE_CURSOR function

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 C-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 C-28 PARSE 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

See Also: OPEN_CURSOR function PARSE procedure FETCH_ROW function GET_VALUE procedure BIND_OUT_VARIABLE procedure BIND_OUT_VARIABLE_RAW procedure BIND_INOUT_VARIABLE procedure BIND_INOUT_VARIABLE_RAW procedure