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

EXEC_SQL.Variable_Value

Description

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 );

where <datatype> is one of the following:


NUMBER
DATE
VARCHAR2

Parameters

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.

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;