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 documents each of them.

This appendix contains these topics:

• BIND_VARIABLE procedure

• BIND_VARIABLE_NCHAR procedure

• BIND_VARIABLE_RAW procedure

• BIND_OUT_VARIABLE procedure

• BIND_OUT_VARIABLE_NCHAR procedure

• BIND_OUT_VARIABLE_RAW procedure

• BIND_INOUT_VARIABLE procedure

• BIND_INOUT_VARIABLE_NCHAR procedure

• BIND_INOUT_VARIABLE_RAW procedure

• CLOSE_CURSOR function

• EXECUTE_IMMEDIATE function

• EXECUTE_NON_QUERY function

• FETCH_ROW function

• GET_VALUE procedure

• GET_VALUE_NCHAR procedure

• GET_VALUE_RAW procedure

• OPEN_CURSOR function

• PARSE procedure

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_NCHAR procedure	Binds IN variables of type NVARCHAR2.
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_NCHAR procedure	Binds an OUT variable of data type NVARCHAR2 with a PL/SQL program variable.
BIND_OUT_VARIABLE_RAW procedure	Binds an OUT variable of data type RAW with a PL/SQL program variable.
BIND_INOUT_VARIABLE procedure	Binds IN OUT bind variables.
BIND_INOUT_VARIABLE_NCHAR procedure	Binds IN OUT bind variables of data type NVARCHAR2
BIND_INOUT_VARIABLE_RAW procedure	Binds IN OUT bind variables of data type 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_NCHAR 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 NVARCHAR2 data type
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 data type
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

See Also: OPEN_CURSOR function PARSE procedure BIND_VARIABLE_NCHAR procedure BIND_VARIABLE_RAW procedure

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);

Where dty is one of the following data types:

• DATE

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

• NUMBER

• TIMESTAMP

• TIMESTAMP WITH TIMEZONE

• TIMESTAMP WITH LOCAL TIMEZONE

• VARCHAR2

  See Also: BIND_VARIABLE_NCHAR procedure 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

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

BIND_VARIABLE_NCHAR procedure

This procedure binds IN variables of type NVARCHAR2.

Syntax

DBMS_HS_PASSTHROUGH.BIND_VARIABLE_NCHAR (
     c      IN    BINARY_INTEGER NOT NULL,
     pos    IN    BINARY_INTEGER NOT NULL,
     val    IN    NVARCHAR2);

Parameters

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

Exceptions

Table C-5 BIND_VARIABLE_NCHAR 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_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);

Parameters

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

Exceptions

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

Syntax

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

Where dty is one of

• DATE

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

• NUMBER

• TIMESTAMP

• TIMESTAMP WITH TIMEZONE

• TIMESTAMP WITH LOCAL TIMEZONE

• VARCHAR2

  See Also: BIND_INOUT_VARIABLE_NCHAR procedure for more information about OUT variables of data type RAW.

Parameters

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

Exceptions

Table C-9 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_INOUT_VARIABLE_NCHAR procedure BIND_VARIABLE procedure BIND_VARIABLE_NCHAR procedure GET_VALUE procedure

BIND_OUT_VARIABLE_NCHAR procedure

This procedure binds an OUT variable of data type NVARCHAR2 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   NVARCHAR2);

Parameters

Table C-10 BIND_OUT_VARIABLE_NCHAR 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.

Exceptions

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

BIND_OUT_VARIABLE_RAW procedure

This procedure binds an OUT variable of data type 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);

Parameters

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

Exceptions

Table C-13 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_NCHAR procedure GET_VALUE procedure

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>);
  

Where dty is one of

• DATE

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

• NUMBER

• TIMESTAMP

• TIMESTAMP WITH TIMEZONE

• TIMESTAMP WITH LOCAL TIMEZONE

• VARCHAR2

Parameters

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

Exceptions

Table C-15 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_NCHAR procedure BIND_OUT_VARIABLE procedure BIND_INOUT_VARIABLE_NCHAR procedure BIND_VARIABLE procedure BIND_VARIABLE_NCHAR procedure GET_VALUE procedure

BIND_INOUT_VARIABLE_NCHAR procedure

This procedure binds IN OUT bind variables of data type NVARCHAR2.

Syntax

DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE_NCHAR (
     c        IN     BINARY_INTEGER NOT NULL,
     pos      IN     BINARY_INTEGER NOT NULL,
     val      IN OUT NVARCHAR2);

Parameters

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

Exceptions

Table C-17 BIND_INOUT_VARIABLE_NCHAR 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_INOUT_VARIABLE_NCHAR procedure BIND_VARIABLE procedure BIND_VARIABLE_NCHAR procedure GET_VALUE procedure

BIND_INOUT_VARIABLE_RAW procedure

This procedure binds IN OUT bind variables of data type RAW.

Syntax

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

Parameters

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

Exceptions

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

Pragmas defined: WNDS, RNDS

See Also: OPEN_CURSOR function PARSE procedure BIND_INOUT_VARIABLE procedure BIND_OUT_VARIABLE procedure BIND_INOUT_VARIABLE_NCHAR procedure BIND_VARIABLE procedure BIND_VARIABLE_NCHAR 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-20 CLOSE_CURSOR Procedure Parameters

Parameter	Description
c	Cursor to be released.

Exceptions

Table C-21 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 statement 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-22 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-23 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 BIND_INOUT_VARIABLE procedure

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-24 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 C-25 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-26 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-27 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

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

• NUMBER

• TIMESTAMP

• TIMESTAMP WITH TIMEZONE

• TIMESTAMP WITH LOCAL TIMEZONE

• VARCHAR2

For retrieving values of data type RAW, see GET_VALUE_RAW.

Parameters

Table C-28 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-29 GET_VALUE 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_NCHAR procedure BIND_INOUT_VARIABLE_NCHAR procedure BIND_INOUT_VARIABLE_RAW procedure

GET_VALUE_NCHAR procedure

This procedure, which operates on NVARCHAR2 data types, 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_NCHAR (
     c      IN    BINARY_INTEGER NOT NULL,
     pos    IN    BINARY_INTEGER NOT NULL,
     val    OUT   NVARCHAR2);

Parameters

Table C-30 GET_VALUE_NCHAR 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-31 GET_VALUE_NCHAR 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 GET_VALUE_RAW procedure BIND_INOUT_VARIABLE_NCHAR procedure BIND_INOUT_VARIABLE_RAW procedure

GET_VALUE_RAW procedure

This procedure, which operates on RAW data types, 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-32 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-33 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 GET_VALUE_NCHAR procedure BIND_INOUT_VARIABLE_NCHAR 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-34 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: BIND_INOUT_VARIABLE procedure

PARSE procedure

This procedure parses a 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-35 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-36 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_INOUT_VARIABLE_NCHAR procedure BIND_INOUT_VARIABLE_NCHAR procedure