Oracle Heterogeneous Services Release 8.1.7 Part Number A88714-01 |
Library | Service | Contents | Index

# 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

| Copyright © 1996-2001 Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index