The VARIABLE_VALUE procedure is used to obtain the value of an output variable after executing a dynamic SQL statement.
Syntax
DBMS_SQL.VARIABLE_VALUE(
cursor IN NUMBER,
variable IN VARCHAR2,
value OUT value_type
);
Parameters
| Parameter | Description |
|---|---|
| cursor | The ID of an open dynamic SQL cursor. |
| variable | The name of the variable to be obtained. |
| value | The target variable for storing the return value. |
Examples
Execute the following commands to create a table tab with four columns, insert data into the table using the single_Row_insert stored procedure with dynamic SQL, and return the calculated value (c1*c2) and the inserted date value to the output parameter using the RETURNING clause. Finally, output the results.
create table tab(c1 number, c2 number, c3 date, c4 varchar2(10));
delimiter /;
CREATE OR REPLACE PROCEDURE single_Row_insert
(c1 NUMBER, c2 NUMBER, c3 date, c4 varchar2, r1 OUT NUMBER, r2 OUT date, r3 OUT varchar2) is
c NUMBER;
n NUMBER;
begin
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c, 'INSERT INTO tab VALUES (:bnd1, :bnd2, :bnd3, :bnd4) ' ||
'RETURNING c1*c2, c3 INTO :bnd5, :bnd6', DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(c, 'bnd1', c1);
DBMS_SQL.BIND_VARIABLE(c, 'bnd2', c2);
DBMS_SQL.BIND_VARIABLE(c, 'bnd3', c3);
DBMS_SQL.BIND_VARIABLE(c, 'bnd4', c4);
DBMS_SQL.BIND_VARIABLE(c, 'bnd5', r1);
DBMS_SQL.BIND_VARIABLE(c, 'bnd6', r2);
n := DBMS_SQL.EXECUTE(c);
DBMS_SQL.VARIABLE_VALUE(c, 'bnd5', r1);-- get value of outbind variable
DBMS_SQL.VARIABLE_VALUE(c, 'bnd6', r2);-- get value of outbind variable
DBMS_SQL.CLOSE_CURSOR(c);
END;
/
DECLARE
a number;
b date;
c varchar2(10);
BEGIN
single_Row_insert(2, 4, to_date('2020-01-01', 'yyyy-mm-dd'), 'Tomorrow', a, b, c);
dbms_output.put_line(a || ' --- ' || b || ' --- ' || c);
end;
/