Oracle9i Heterogeneous Connectivity Administrator's Guide Release 1 (9.0.1) Part Number A88789_01 |
|
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
DBMS_HS
Package Subprograms
BIND_VARIABLE
procedure
This procedure binds an IN
variable positionally with a PL/SQL program variable.
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
BIND_VARIABLE
Procedure Parameters
BIND_VARIABLE
Procedure Exceptions
Purity levels defined: WNDS, RNDS
BIND_VARIABLE_NCHAR
procedure
This procedure binds IN
variables of type NVARCHAR2
.
DBMS_HS_PASSTHROUGH.BIND_VARIABLE_NCHAR ( c IN BINARY_INTEGER NOT NULL, pos IN BINARY_INTEGER NOT NULL, val IN NVARCHAR2);
BIND_VARIABLE_NCHAR
Procedure Parameters
BIND_VARIABLE_NCHAR
Procedure Exceptions
Purity level defined: WNDS, RNDS
BIND_VARIABLE_RAW
procedure
This procedure binds IN
variables of type RAW
.
DBMS_HS_PASSTHROUGH.BIND_VARIABLE_RAW ( c IN BINARY_INTEGER NOT NULL, pos IN BINARY_INTEGER NOT NULL, val IN RAW);
BIND_VARIABLE_RAW
Procedure Parameters
BIND_VARIABLE_RAW
Procedure Exceptions
Purity level defined: WNDS, RNDS
BIND_OUT_VARIABLE
procedure
This procedure binds an OUT
variable with a PL/SQL program variable.
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
BIND_OUT_VARIABLE
Procedure Parameters
BIND_OUT_VARIABLE
Procedure Exceptions
Purity level defined: WNDS, RNDS
BIND_OUT_VARIABLE_NCHAR
procedure
This procedure binds an OUT
variable of data type NVARCHAR2
with a PL/SQL program variable.
DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE ( c IN BINARY_INTEGER NOT NULL, pos IN BINARY_INTEGER NOT NULL, val OUT NVARCHAR2);
BIND_OUT_VARIABLE_NCHAR
Procedure Parameters
BIND_OUT_VARIABLE_NCHAR
Parameter Exceptions
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.
DBMS_HS_PASSTHROUGH.BIND_OUT_VARIABLE ( c IN BINARY_INTEGER NOT NULL, pos IN BINARY_INTEGER NOT NULL, val OUT RAW);
BIND_OUT_VARIABLE_RAW
Procedure Parameters
BIND_OUT_VARIABLE_RAW
Parameter Exceptions
Pragmas defined: WNDS, RNDS
BIND_INOUT_VARIABLE
procedure
This procedure binds IN OUT
bind variables.
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
BIND_INOUT_VARIABLE
Procedure Parameters
BIND_INOUT_VARIABLE
Procedure Exceptions
Purity level defined: WNDS, RNDS
BIND_INOUT_VARIABLE_NCHAR
procedure
This procedure binds IN OUT
bind variables of data type NVARCHAR2
.
DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE_NCHAR ( c IN BINARY_INTEGER NOT NULL, pos IN BINARY_INTEGER NOT NULL, val IN OUT NVARCHAR2);
BIND_INOUT_VARIABLE_NCHAR
Procedure Parameters
Pragmas defined: WNDS, RNDS
BIND_INOUT_VARIABLE_RAW
procedure
This procedure binds IN OUT
bind variables of data type RAW
.
DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE_RAW ( c IN BINARY_INTEGER NOT NULL, pos IN BINARY_INTEGER NOT NULL, val IN OUT RAW);
BIND_INOUT_VARIABLE_RAW
Procedure Parameters
BIND_INOUT_VARIABLE_RAW
Procedure Exceptions
Pragmas defined: WNDS, RNDS
CLOSE_CURSOR
functionThis 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.
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR ( c IN BINARY_INTEGER NOT NULL);
CLOSE_CURSOR
Procedure Parameters
Parameter | Description |
---|---|
c |
Cursor to be released. |
CLOSE_CURSOR
Procedure Exceptions
Exception | Description |
---|---|
ORA-28555 |
A |
Purity level defined: WNDS, RNDS
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
.
EXECUTE_IMMEDIATE ( s IN VARCHAR2 NOT NULL ) RETURN BINARY_INTEGER);
EXECUTE_IMMEDIATE
Procedure Parameters
Parameter | Description |
---|---|
|
|
The number of rows affected by the execution of the SQL statement.
EXECUTE_IMMEDIATE
Procedure Exceptions
Exception | Description |
---|---|
|
Max open cursors. |
|
SQL statement is invalid. |
|
A |
Purity level defined: NONE
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.
DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY ( c IN BINARY_INTEGER NOT NULL) RETURN BINARY_INTEGER);
Parameter | Description |
---|---|
|
Cursor associated with the pass-through SQL statement. Cursor must be opened and parsed, using the routines |
The number of rows affected by the SQL statement in the non-Oracle system.
Purity level defined: NONE
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:
DBMS_HS_PASSTHROUGH.FETCH_ROW ( c IN BINARY_INTEGER NOT NULL [,first IN BOOLEAN]) RETURN BINARY_INTEGER);
The returns the number of rows fetched. The function will return 0 if the last row was already fetched.
Purity level defined: WNDS
GET_VALUE
procedureThis procedure has two purposes:
SELECT
statements after a row has been fetched.
OUT
bind values after the SQL statement has been executed.
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
.
Purity level defined: WNDS
GET_VALUE_NCHAR
procedure
This procedure, which operates on NVARCHAR2
data types, has two purposes:
SELECT
statements after a row has been fetched.
OUT
bind values after the SQL statement has been executed.
DBMS_HS_PASSTHROUGH.GET_VALUE_NCHAR ( c IN BINARY_INTEGER NOT NULL, pos IN BINARY_INTEGER NOT NULL, val OUT NVARCHAR2);
Purity level defined: WNDS
GET_VALUE_RAW
procedure
This procedure, which operates on RAW
data types, has two purposes:
SELECT
statements after a row has been fetched.
OUT
bind values after the SQL statement has been executed.
DBMS_HS_PASSTHROUGH.GET_VALUE_RAW ( c IN BINARY_INTEGER NOT NULL, pos IN BINARY_INTEGER NOT NULL, val OUT RAW);
Purity level defined: WNDS
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
.
DBMS_HS_PASSTHROUGH.OPEN_CURSOR () RETURN BINARY_INTEGER;
The cursor to be used on subsequent procedure and function calls.
Exception | Description |
---|---|
|
Maximum number of open cursor has been exceeded. Increase Heterogeneous Services |
Purity level defined: WNDS, RNDS
PARSE
procedureThis procedure parses a SQL statement at non-Oracle system.
DBMS_HS_PASSTHROUGH.GET_VALUE_RAW ( c IN BINARY_INTEGER NOT NULL, stmt IN VARCHAR2 NOT NULL);
Parameter | Description |
---|---|
|
Cursor associated with the pass-through SQL statement. Cursor must be opened using function |
|
Statement to be parsed. |
Exception | Description |
---|---|
|
The cursor passed is invalid. |
|
SQL statement is illegal. |
|
A |
Purity level defined: WNDS, RNDS
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|