Make a PL/SQL stored procedure roll back all changes when an exception occurs?

Question

I have a procedure that makes a number of updates to different tables. I want all of the changes to be rolled back should any error occur anywhere in the procedure. Therefore I utilized this structure:

CREATE PROCEDURE foo (x NUMBER) IS
BEGIN
  -- Do some inserts here.
  INSERT INTO bar VALUES (x);
  -- Sometimes there might be an error.
  IF x = 3 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Wooops...');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    --Rollback all the changes and then raise the error again.
    ROLLBACK;
    RAISE;
END foo;

The problem is that this rolls back everything that has been done since the last commit, not just the changes made by the procedure. For instance, this will insert 4 and 5, but 1 and 2 will be rolled back:

BEGIN
  FOR x IN 1..5 LOOP
    BEGIN
      foo(x);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;

How can I make the procedure roll back only the changes the procedure did during this call? I guess I should use transactions somehow, but I am not sure how to set it up.

Please note that I want to fix this in the code for the procedure, and not in the code that calls it.

Answer 1

In Oracle you can use SAVEPOINTS. It would be this:

CREATE PROCEDURE foo (x NUMBER) IS
BEGIN
  SAVEPOINT update_bar;

  -- Do some inserts here.
  INSERT INTO bar VALUES (x);
  -- Sometimes there might be an error.
  IF x = 3 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Wooops...');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    -- Rollback everything which was made after `SAVEPOINT update_bar`
    ROLLBACK TO update_bar;
    RAISE;
END foo;