CREATE TABLE t (n NUMBER UNIQUE)
Table created.
DECLARE
two_ts DBMS_SQL.number_table := DBMS_SQL.number_table (1=>1,2=>2);
BEGIN
FORALL indx IN 1 .. 2
SAVE EXCEPTIONS
INSERT INTO t VALUES (two_ts(indx));
DBMS_OUTPUT.put_line (SQL%ROWCOUNT || ' inserted');
ROLLBACK;
EXCEPTION
WHEN others
THEN
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.put_line
(SQLERRM (SQL%BULK_EXCEPTIONS (indx).ERROR_CODE));
END LOOP;
END;
Statement processed.
2 inserted
DECLARE
two_ts DBMS_SQL.number_table := DBMS_SQL.number_table (1=>1,2=>1);
BEGIN
FORALL indx IN 1 .. 2
SAVE EXCEPTIONS
INSERT INTO t VALUES (two_ts(indx));
EXCEPTION
WHEN OTHERS
THEN
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.put_line
('Not inserted: ' || SQLERRM (SQL%BULK_EXCEPTIONS (indx).ERROR_CODE));
END LOOP;
ROLLBACK;
END;
Statement processed.
Not inserted: User-Defined Exception
DECLARE
two_ts DBMS_SQL.number_table := DBMS_SQL.number_table (1=>1,2=>1);
BEGIN
FORALL indx IN 1 .. 2
SAVE EXCEPTIONS
INSERT INTO t VALUES (two_ts(indx));
EXCEPTION
WHEN others
THEN
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.put_line
('Not inserted: ' || SQLERRM (- SQL%BULK_EXCEPTIONS (indx).ERROR_CODE));
END LOOP;
ROLLBACK;
END;
Statement processed.
Not inserted: ORA-00001: unique constraint (.) violated
"Pretty printing" contents of SQL%BULK_EXCEPTIONS
declare
TYPE nested_typ IS TABLE OF NUMBER;
err_lst nested_typ :=nested_typ();
two_ts dbms_sql.number_table := dbms_sql.number_table(1=>1,2=>2);
begin
forall indx in 1 .. 2
save exceptions
insert into t values (two_ts(indx));
exception
when others
then
for indx in 1 .. SQL%BULK_EXCEPTIONS.COUNT
loop
if nvl(err_lst.last,0) < SQL%BULK_EXCEPTIONS(indx).ERROR_CODE then
err_lst.extend(SQL%BULK_EXCEPTIONS(indx).ERROR_CODE-nvl(err_lst.last,0));
end if;
err_lst(SQL%BULK_EXCEPTIONS(indx).ERROR_CODE) := nvl(err_lst(SQL%BULK_EXCEPTIONS(indx).ERROR_CODE),0)+1;
end loop;
for indx in err_lst.first .. err_lst.last
loop
if err_lst(indx) is not null then
dbms_output.put_line('Error "'||SQLERRM (-indx)||'" occurred '|| err_lst(indx) ||' time(s)');
end if;
end loop;
end;
Statement processed.