EXEC_SQL.VARIABLE_VALUE
built-in procedureThis procedure retrieves the output value of a named bind variable at a specified cursor. It also returns the values of bind variables in anonymous PL/SQL blocks.
PROCEDURE EXEC_SQL.VARIABLE_VALUE
([connid IN CONNTYPE],
curs_id IN CURSTYPE,
name IN VARCHAR2,
value OUT <datatype>);
where <datatype>
is one of the following:
NUMBER
DATE
VARCHAR2
Parameter |
Mode |
Description |
|
IN |
Is the handle to the connection you want to use. If you
do not specify a connection, |
|
IN |
Is the cursor handle you want to retrieve the bind variable from. |
|
IN |
Is the name of the bind variable. |
|
OUT |
Returns the value of the bind variable for the specified cursor. |
EXEC_SQL.BIND_VARIABLE
, the exception EXEC_SQL.VALUE_ERROR
is raised.
It is assumed that the following procedure, tstbindnum, exists on the server
which is specified by the connection string used in OPEN_CONNECTION
.
Create or replace procedure tstbindnum (input IN
NUMBER
,
output OUT
NUMBER
) as
BEGIN
output := input * 2;
END;
All this procedure does is to take an input number, double its value, and return it in the out variable.
PROCEDURE esvarvalnum (input IN NUMBER) IS
connection_id EXEC_SQL.CONNTYPE;
bIsConnected BOOLEAN;
cursorID EXEC_SQL.CURSTYPE;
sqlstr VARCHAR2(1000);
nRes PLS_INTEGER;
mynum NUMBER;
BEGIN
connection_id := EXEC_SQL.OPEN_CONNECTION('connection_string');
cursorID := EXEC_SQL.OPEN_CURSOR(connection_id);
sqlstr := 'begin tstbindnum(:bn1, :bnret); end;'; -- an anonymous block
EXEC_SQL.PARSE(connection_id, cursorID, sqlstr, exec_sql.V7);
--
-- define input value
--
EXEC_SQL.BIND_VARIABLE(connection_id, cursorID, ':bn1', input);
--
-- set up output value
--
EXEC_SQL.BIND_VARIABLE(connection_id, cursorID, ':bnret', mynum);
nRes := EXEC_SQL.EXECUTE(connection_id, cursorID);
--
-- after the statement is executed, we call VARIABLE_VALUE to obtain the value of the bind variable :bnret
--
EXEC_SQL.VARIABLE_VALUE(connection_id, cursorID, ':bnret', mynum);
EXEC_SQL.CLOSE_CURSOR(connection_id, cursorID);
EXEC_SQL.CLOSE_CONNECTION(connection_id);
END;
About the EXEC_SQL
built-in package
Copyright © 1984, 2005, Oracle. All rights reserved.