Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

On Tue, 2022-10-18 at 17:33 -0400, Tom Lane wrote:
> Bryn Llewellyn <bryn@yugabyte.com> writes:
> > xof@thebuild.com wrote:
> > > You can commit in a loop, but not in BEGIN / END block that has an exception handler: that creates a
subtransactionfor the duration of the BEGIN / END.
 
> 
> > This surprised me when I first started to use PG (after all those years
> > with ORCL).
> 
> Really?  BEGIN with an exception block is a subtransaction because it's
> defined to roll back to the database state as of the start of the block
> if an exception occurs.  COMMIT in the middle fundamentally conflicts
> with that, I should think.  Does Oracle interpret that differently?

Looks like Oracle doesn't care much about that:

SQL> CREATE TABLE mytab (id integer CHECK (id > 0));

Table created.

SQL> CREATE PROCEDURE committest IS
  2  BEGIN
  3     INSERT INTO mytab VALUES (42);
  4     COMMIT;
  5     INSERT INTO mytab VALUES (-42);
  6  EXCEPTION
  7     WHEN OTHERS THEN
  8        NULL;
  9  END;
 10  /

Procedure created.

SQL> CALL committest();

Call completed.

SQL> SELECT * FROM mytab;

    ID
----------
    42

I looks like Oracle allows you to randomly interfere with its transaction handling.
If you run commit and then enter an exception handler, it simply doesn't rollback.

Yours,
Laurenz Albe