Oracle8 Distributed Database Systems
Release 8.0

A58247-01

Library

Product

Contents

Index

Prev Next

C
DBMS_HS_PASSTHROUGH for Pass-Through SQL

This appendix describes the procedures and functions in the package DBMS_HS_PASSTHROUGH for pass-through SQL of Heterogeneous Services. See Chapter 8, "Application Development with Heterogeneous Services" for more information on how to use this package.

Referenced in this appendix are:

DBMS_HS_PASSTHROUGH.BIND_VARIABLE

Purpose

To bind an "IN" variable positionally with a PL/SQL program variable. See Chapter 8, "Application Development with Heterogeneous Services" on how to bind variables.

Interface Description

PROCEDURE 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

To bind RAW variables use the procedure DBMS_HS_PASSTHROUGH.BIND_VARIABLE_RAW

Parameters and Descriptions
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

 

Value that must be passed to the bind variable

 

name

 

Optional parameter to 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. Note that passing the position is still required.

 

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

 

Purity Level

Purity level defined : WNDS, RNDS

See Also

DBMS_HS_PASSTHROUGH.OPEN_CURSOR DBMS_HS_PASSTHROUGH.PARSE DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE DBMS_HS_PASSTHROUGH.BIND_VARIABLE_RAW

DBMS_HS_PASSTHROUGH.BIND_VARIABLE_RAW

Purpose

To bind IN variables of type RAW.

Interface Description

 PROCEDURE BIND_VARIABLE_RAW 
 (c      IN BINARY_INTEGER NOT NULL,
 pos    IN BINARY_INTEGER NOT NULL,
 val    IN RAW 	
  [,name  IN VARCHAR2])

Parameters and Descriptions
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

 

Value that must be passed to the bind variable.

 

name

 

Optional parameter to 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. Note that passing the position is still required.

 


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.

 

Purity Level

Purity level defined : WNDS, RNDS

See Also

DBMS_HS_PASSTHROUGH.OPEN_CURSOR DBMS_HS_PASSTHROUGH.PARSE DBMS_HS_PASSTHROUGH.BIND_VARIABLE DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE

DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE

Purpose

To bind an OUT variable with a PL/SQL program variable. See Chapter 8, "Application Development with Heterogeneous Services" for more information on binding OUT parameters.

Interface Description

PROCEDURE 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

For binding OUT variables of datatype RAW, see BIND_OUT_VARIABLE_RAW

Parameters and Descriptions
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, 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. Note that passing the position is still required.

 

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.

 

Purity Level

Purity level defined : WNDS, RNDS

See Also

DBMS_HS_PASSTHROUGH.OPEN_CURSOR DBMS_HS_PASSTHROUGH.PARSE DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE_RAW DBMS_HS_PASSTHROUGH.BIND_VARIABLE DBMS_HS_PASSTHROUGH.BIND_VARIABLE_RAW DBMS_HS_PASSTHROUGH.GET_VALUE

DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE_RAW

Purpose

To bind an OUT variable of datatype RAW with a PL/SQL program variable. See Chapter 8, "Application Development with Heterogeneous Services" for more information on binding OUT parameters.

Interface Description

PROCEDURE BIND_OUT_VARIABLE 
c        IN  BINARY_INTEGER NOT NULL,
pos      IN  BINARY_INTEGER NOT NULL,
val      OUT RAW,
,name    IN  VARCHAR2])

Parameters and Descriptions
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, 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. Note that passing the position is still required.

 


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.

 

Purity Level

Purity level defined : WNDS, RNDS

See Also

DBMS_HS_PASSTHROUGH.OPEN_CURSOR DBMS_HS_PASSTHROUGH.PARSE DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE DBMS_HS_PASSTHROUGH.BIND_VARIABLE DBMS_HS_PASSTHROUGH.BIND_VARIABLE_RAW DBMS_HS_PASSTHROUGH.GET_VALUE

DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE

Purpose

To bind IN OUT bind variables. See Chapter 8, "Application Development with Heterogeneous Services" for more information on binding IN OUT parameters.

Interface Description

PROCEDURE 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

For binding IN OUT variables of datatype RAW see BIND_INOUT_VARIABLE_RAW.

Parameters and Descriptions
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, 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. Note that passing the position is still required.

 

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.

 

Purity Level

Purity level defined : WNDS, RNDS

See Also

DBMS_HS_PASSTHROUGH.OPEN_CURSOR DBMS_HS_PASSTHROUGH.PARSE DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE_RAW DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE_RAW DBMS_HS_PASSTHROUGH.BIND_VARIABLE DBMS_HS_PASSTHROUGH.BIND_VARIABLE_RAW DBMS_HS_PASSTHROUGH.GET_VALUE

DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE_RAW

Purpose

To bind IN OUT bind variables of datatype RAW. See Chapter 8, "Application Development with Heterogeneous Services" for more information on binding IN OUT parameters.

Interface Description

PROCEDURE BIND_INOUT_VARIABLE 
c        IN     BINARY_INTEGER NOT NULL,
pos       IN     BINARY_INTEGER NOT NULL,
val       IN OUT RAW,
[,name    IN     VARCHAR2]);

Parameters and Descriptions
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, 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. Note that passing the position is still required.

 


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.

 

Purity Level

Purity level defined : WNDS, RNDS

See Also

DBMS_HS_PASSTHROUGH.OPEN_CURSOR DBMS_HS_PASSTHROUGH.PARSE DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE_RAW, DBMS_HS_PASSTHROUGH.BIND_VARIABLE DBMS_HS_PASSTHROUGH.BIND_VARIABLE_RAW DBMS_HS_PASSTHROUGH.GET_VALUE

DBMS_HS_PASSTHROUGH.CLOSE_CURSOR

Purpose

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

Interface Description

PROCEDURE CLOSE_CURSOR (c IN BINARY_INTEGER NOT NULL);

Parameter Description
Parameter   Description  

c

 

Cursor to be released.

 

Exceptions
Exception   Description  

ORA-28555

 

A NULL value was passed for a NOT NULL parameter.

 

Purity Level

Purity level defined : WNDS, RNDS

See Also

DBMS_HS_PASSTHROUGH.OPEN_CURSOR

DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE

Purpose

This function executes a SQL statement immediately. Any valid SQL command except SELECT can be executed 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 executed using the PASSTHROUGH SQL protocol sequence of OPEN_CURSOR, PARSE, EXECUTE_NON_QUERY, CLOSE_CURSOR.

Interface Description

FUNCTION EXECUTE_IMMEDIATE ( S IN VARCHAR2 NOT NULL )  
RETURN BINARY_INTEGER; 

Parameter Description
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:
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.

 

Purity Level

Purity level defined : NONE

See Also

DBMS_HS_PASSTHROUGH.OPEN_CURSOR
DBMS_HS_PASSTHROUGH.PARSE
DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR

DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY

Purpose

This function executes 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 executed.

Interface Description

FUNCTION EXECUTE_NON_QUERY (c IN BINARY_INTEGER NOT NULL)
RETURN   BINARY_INTEGER

Parameter Description
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
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.

 

Purity Level

Purity level defined : NONE

See Also

DBMS_HS_PASSTHROUGH.OPEN_CURSOR DBMS_HS_PASSTHROUGH.PARSE

DBMS_HS_PASSTHROUGH.FETCH_ROW

Purpose

To fetch 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.

Interface Description

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

Parameters and Descriptions
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 reexecute SELECT statement. Possible values:

  • TRUE: reexecute 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
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.

 

Purity Level

Purity level defined : WNDS

See Also

DBMS_HS_PASSTHROUGH.OPEN_CURSOR DBMS_HS_PASSTHROUGH.PARSE

DBMS_HS_PASSTHROUGH.GET_VALUE

Purpose

This procedure has two purposes:

Interface Description

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

Where <dty> is one of

For retrieving values of datatype RAW, see GET_VALUE_RAW.

Parameters and Descriptions
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
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.

 

Purity Level

Purity level defined : WNDS

See Also

DBMS_HS_PASSTHROUGH.OPEN_CURSOR DBMS_HS_PASSTHROUGH.PARSE
DBMS_HS_PASSTHROUGH.FETCH_ROW DBMS_HS_PASSTHROUGH.GET_VALUE_RAW DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE_RAW DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE_RAW

DBMS_HS_PASSTHROUGH.GET_VALUE_RAW

Purpose

Similar to GET_VALUE, but for datatype RAW.

Interface Description

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

Parameters and Descriptions
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
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.

 

Purity Level

Purity level defined : WNDS

See Also

DBMS_HS_PASSTHROUGH.OPEN_CURSOR DBMS_HS_PASSTHROUGH.PARSE
DBMS_HS_PASSTHROUGH.FETCH_ROW DBMS_HS_PASSTHROUGH.GET_VALUE DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE_RAW DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE_RAW

DBMS_HS_PASSTHROUGH.OPEN_CURSOR

Purpose

To open 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.

Interface Description

FUNCTION OPEN_CURSOR RETURN   BINARY_INTEGER;

Returns

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

Exceptions
Exception   Description  

ORA-28554

 

Maximum number of open cursor has been exceeded. Increase Heterogeneous Services' OPEN_CURSORS initialization parameter.

 

Purity Level

Purity level defined : WNDS, RNDS

See Also

DBMS_HS_PASSTHROUGH.CLOSE_CURSOR

DBMS_HS_PASSTHROUGH.PARSE

Purpose

To parse SQL statement at non-Oracle system.

Interface Description

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

Parameters and Descriptions
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
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.

 

Purity Level

Purity level defined : WNDS, RNDS

See Also

DBMS_HS_PASSTHROUGH.OPEN_CURSOR DBMS_HS_PASSTHROUGH.PARSE
DBMS_HS_PASSTHROUGH.FETCH_ROW DBMS_HS_PASSTHROUGH.GET_VALUE DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE_RAW DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE_RAW




Prev

Next
Oracle
Copyright © 1997 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index