Dienstag, 10. November 2009

rollback in PL/SQL

I recently came across a slightly, but possible dangerous mismatch between transactions and the PL/SQL code based on it.
Here a cut down example (not very realistic, but easy to follow):

create table mytab (myval number);

create or replace procedure tuwas (
varA in number,
varB in number,
varC OUT number)
is
begin
varC := varA;
insert into mytab values (varA);
if varB = 0 then
commit;
else
rollback;
end if;
end;
/

The Idea behind the code is, to get varA and varB as input parameters, do some calculations (missing here), insert the result into a table and return the calculated value for next steps.
There might be reasons to rollback the DML within the procedure, which is not seen as an error.

And here the result of the procedure:

set serverout on

declare
vA number;
vB number;
vC number;
begin
vA := 1;
vB := 0; -- commit;
tuwas(vA, vB, vC);
dbms_output.put_line('vC: ' ||vC);
vA := 2;
vB := 1; -- rollback;
tuwas(vA, vB, vC);
dbms_output.put_line('vC: ' ||vC);
END;
/

vC: 1
vC: 2

select * from mytab;

MYVAL
----------
1


What's dangerous here? Even with

vA := 2;
vB := 1;
the return value

vC: 2
is given. But t's not visible in the table, as the procedure did a rollback.
I learned from this example never to populate return-values until you are sure it's really stored (if this is a requirement, of course).
Kommentar veröffentlichen