Thursday, November 19, 2009

The amazing disappearing row trick

This curiosity was prompted by a question on stackoverflow
The code inserts a row into a table and then raises an exception.
Despite the exception being caught by an exception handler, and with no explicit rollback, the inserted row has vanished.


drop table dummy;

create table dummy (id number);

DECLARE
v_num NUMBER;
begin
begin
execute immediate
'declare
v_num number(2);
begin
insert into dummy values (1);
dbms_output.put_line(SQL%ROWCOUNT);
v_num := 100;
end;';
exception
when value_error then null;
end;
select count(*) into v_num from dummy;
dbms_output.put_line(v_num);
end;
/


The simple answer is that EXECUTE IMMEDIATE executes an SQL statement (which may be an anonymous PL/SQL block), and that statement will either succeed or fail. If it errors, atomicity states that changes made as part of that statement must be rolled back.

Without the EXECUTE IMMEDIATE, the entirety of the code is a single statement and there is no need for a rollback, since the statement succeeds due to the exception handler.


DECLARE
v_num NUMBER;
begin
begin
declare
v_num number(2);
begin
insert into dummy values (1);
dbms_output.put_line('X:'||SQL%ROWCOUNT);
v_num := 100;
end;
exception
when value_error then null;
end;
select count(*) into v_num from dummy;
dbms_output.put_line('Y:'||v_num);
--
end;
/


PS. You get the same result using EXECUTE IMMEDIATE or DBMS_SQL.

No comments: