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.