VARIABLE_VALUE Procedure

This procedure returns the value of the named variable for a given cursor. It is used to return the values of bind variables inside PL/SQL blocks or DML statements with returning clause.

Syntax

DBMS_SQL.VARIABLE_VALUE (
   c               IN  INTEGER,
   name            IN  VARCHAR2,
   value           OUT <datatype>);
 

Where <datatype> can be any one of the following types:

NUMBER
DATE
VARCHAR2 CHARACTER SET ANY_CS 
BLOB 
CLOB CHARACTER SET ANY_CS 
BFILE 
UROWID

Pragmas

pragma restrict_references(variable_value,RNDS,WNDS);

The following syntax is also supported for the VARIABLE_VALUE procedure:

DBMS_SQL.VARIABLE_VALUE ( 
   c                 IN  INTEGER, 
   name              IN  VARCHAR2,
   <table_variable>  IN  <datatype>); 

Where the <table_variable> and its corresponding <datatype> can be any one of these matching pairs:

<num_tab>      Number_Table
<vchr2_tab>    Varchar2_Table
<date_tab>     Date_Table
<blob_tab>     Blob_Table
<clob_tab>     Clob_Table
<bfile_tab>    Bfile_Table 
<urowid_tab>   Urowid_Table

For variables containing CHAR, RAW, and ROWID data, you can use the following variations on the syntax:

DBMS_SQL.VARIABLE_VALUE_CHAR (
   c               IN  INTEGER,
   name            IN  VARCHAR2,
   value           OUT CHAR CHARACTER SET ANY_CS);

DBMS_SQL.VARIABLE_VALUE_RAW (
   c               IN  INTEGER,
   name            IN  VARCHAR2,
   value           OUT RAW);

DBMS_SQL.VARIABLE_VALUE_ROWID (
   c               IN  INTEGER,
   name            IN  VARCHAR2,
   value           OUT ROWID);

Parameters

Table 62-13 VARIABLE_VALUE Procedure Parameters

Parameter	Description
c	ID number of the cursor from which to get the values.
name	Name of the variable for which you are retrieving the value.
value	Returns the value of the variable for the specified position. Oracle raises exception ORA-06562, inconsistent_type, if the type of this output parameter differs from the actual type of the value, as defined by the call to BIND_VARIABLE.
position	Relative position of the column in the cursor. The first column in a statement has position 1.