Rollback Behavior
When a FORALL statement fails, the database changes are rolled back to an implicit savepoint marked before each of the SQL statement execution. The Changes that are made during the earlier executions are not rolled back. For illustration, assume that you create a database table that stores department numbers and the job titles, as shown below:
CREATE TABLE emp2 (deptno NUMBER(2), job VARCHAR2(15));
Next, you insert some rows into the table, as follows:
INSERT INTO emp2 VALUES(10, 'Clerk');
INSERT INTO emp2 VALUES(10, 'Clerk');
INSERT INTO emp2 VALUES(20, 'Bookkeeper'); -- 10-char job title
INSERT INTO emp2 VALUES(30, 'Analyst');
INSERT INTO emp2 VALUES(30, 'Analyst');
Then, you attempt to append the 7-character string ' (temp)' to certain job titles using the UPDATE statement which is shown below:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20);
BEGIN
FORALL j IN depts.FIRST..depts.LAST
UPDATE emp2 SET job = job || ' (temp)'
WHERE deptno = depts(j);
-- raises a "value too large" exception
EXCEPTION
WHEN OTHERS THEN
COMMIT;
END;