SAVEPOINT

Return to a previous point in the transaction 	SAVEPOINT 

The rollback short-cut 'roll' does not work with savepoint
CREATE TABLE t1 (
testcol  NUMBER);

DECLARE
 i INTEGER := 3;
BEGIN
  INSERT INTO t1 (testcol) VALUES (10/i);

  SAVEPOINT A;

  i := i-1;
  INSERT INTO t1 (testcol) VALUES (10/i);

  i := i-1;
  INSERT INTO t1 (testcol) VALUES (10/i);
/*
  i := i-1;
  INSERT INTO t1 (testcol) VALUES (10/i);

  i := i-1;
  INSERT INTO t1 (testcol) VALUES (10/i);
*/
  COMMIT;
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    ROLLBACK TO SAVEPOINT A;
    COMMIT;
END testblock;
/

SELECT * FROM t1;

TRUNCATE TABLE t1;

DECLARE
 i INTEGER := 3;
BEGIN
  INSERT INTO t1 (testcol) VALUES (10/i);

  SAVEPOINT A;

  i := i-1;
  INSERT INTO t1 (testcol) VALUES (10/i);

  i := i-1;
  INSERT INTO t1 (testcol) VALUES (10/i);

  i := i-1;
  INSERT INTO t1 (testcol) VALUES (10/i);

  i := i-1;
  INSERT INTO t1 (testcol) VALUES (10/i);

  COMMIT;
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    ROLLBACK TO SAVEPOINT A;
    COMMIT;
END testblock;
/

SELECT * FROM t1;