show you BULK_EXCEPTIONS

see below

[@more@]

declare /* relies on... create table t ( text varchar2(3) ) */
type words_t is table of varchar2(10);
words words_t :=
words_t ( 'dog', 'fish', 'cat', 'ball', 'bat', 'spoke', 'pad' )
/* 'ball' and 'spoke' will raise ORA-01401 */;
n integer := 0;

type error_indexes_t is table of integer index by binary_integer;
error_indexes error_indexes_t;
type error_codes_t is table of varchar2(255) index by binary_integer;
error_codes error_codes_t;
type error_context_t is table of varchar2(255) index by binary_integer;
error_context error_context_t;
begin
for j in words.first..words.last
loop

begin
insert into t ( text ) values ( words(j) );
exception when others then
n := n+1; error_indexes(n) := j; error_codes(n) := SQLERRM;error_context(n):= words(j);
end;
end loop;
for j in 1..n
loop

Dbms_Output.Put_Line ( error_indexes(j) || ': ' || error_codes(j)||' error_context :='|| error_context(j));
end loop;
end;

--------at previous version handle dml exception only row by row

declare
type words_t is table of varchar2(10);
words words_t :=
words_t ( 'dog', 'fish', 'cat', 'ball', 'bat', 'spoke', 'pad' );
bulk_errors exception;
pragma exception_init ( bulk_errors, -24381 );
begin
forall j in words.first..words.last
save exceptions
insert into t ( text ) values ( words(j) );
exception when bulk_errors then

for m in 1..sql%bulk_exceptions.count
loop
Dbms_Output.Put_Line (
sql%bulk_exceptions(m).error_index || ', ' ||' error_row: '||words(m)||' '||
Sqlerrm(-sql%bulk_exceptions(m).error_code) );
end loop;
end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/66865/viewspace-933752/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/66865/viewspace-933752/