A script-enabled browser is required for this page to function properly.

EXEC_SQL.VARIABLE_VALUE built-in procedure

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.

Syntax


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

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

Usage notes

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.

Example

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;

See also

About the EXEC_SQL built-in package

EXEC_SQL built-in package