This 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 );
where <datatype> is one of the following:
NUMBER
DATE
VARCHAR2
Name | Mode | Description |
---|---|---|
Connid | IN | Is the handle to the connection you want to use. If you do not specify a connection, EXEC_SQL.Default_Connection retrieves the primary Oracle Developer connection handle from the cache. |
Curs_Id | IN | Is the cursor handle you want to retrieve the bind variable from. |
Name | IN | Is the name of the bind variable. |
Value | OUT | Returns the value of the bind variable for the specified cursor. |
If you try to retrieve a data type other than what was specified for the bind variable by 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;