title: Make a PL/SQL stored procedure roll back all changes when an exception occurs?
body: <p>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:</p>
  
  <pre class="lang-sql prettyprint-override"><code>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;
  </code></pre>
  
  <p>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:</p>
  
  <pre class="lang-sql prettyprint-override"><code>BEGIN
    FOR x IN 1..5 LOOP
      BEGIN
        foo(x);
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;
    END LOOP;
  END;
  </code></pre>
  
  <p>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.</p>
  
  <p>Please note that I want to fix this in the code for the procedure, and not in the code that calls it.</p>