Oracle: Rollback only crash iteration of loop
I need help to accomplish rollback on iteration if one or more iterations
crash and to commit all others iteration if they success. If crash, it
will rollback whole transaction. Think this can be done with Savepoints,
but I'm not very familiar with them. This is a basic example what i try to
achieve.
DECLARE
...
BEGIN
FOR i IN 1 .. 10
LOOP
BEGIN
-- Some DML and stored procs with DML
INSERT INTO a .. .;
INSERT INTO b .. .;
INSERT INTO a .. .;
DELETE FROM a .. .;
UPDATE INTO c .. .;
m_package.some_proc_with_dml;
EXCEPTION
WHEN OTHERS THEN
merror := merror + || ', ' || + sqlerrm;
miserror := TRUE;
END;
END LOOP;
COMMIT;
IF miserror THEN
raise_application_error(-20000, merror);
END IF;
END;
Thanks in Advance.
No comments:
Post a Comment