The VARIABLE_VALUE stored procedure is used to obtain the value of an output variable after a dynamic SQL statement is executed.
Syntax
DBMS_SQL.VARIABLE_VALUE(
cursor IN NUMBER,
variable IN VARCHAR2,
value OUT value_type
);
Parameters
| Parameter | Description |
|---|---|
| cursor | The ID of the dynamic SQL cursor that is open. |
| variable | The name of the variable to be obtained. |
| value | The variable to store the return value. |
Examples
Execute the following command to create a table named tab that contains four columns, and use the single_Row_insert stored procedure to insert data into the table by using dynamic SQL. The RETURNING clause is used to return the calculated value (c1*c2) and the inserted date value to the output parameters, and the result is then output.
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;
/