create table employees as select * from hr.employees
declare
type namelist_t is table of varchar2 (5000);
enames_with_errors namelist_t
:= namelist_t ('ABC'
, 'DEF'
, rpad ('BIGBIGGERBIGGEST', 1000, 'ABC')
, 'GHI'
);
begin
forall indx in 1 .. enames_with_errors.count
update employees
set first_name = enames_with_errors (indx);
rollback;
exception
when others
then
dbms_output.put_line ('Updated ' || sql%rowcount || ' rows.');
dbms_output.put_line (dbms_utility.format_error_stack);
rollback;
end;
declare
type namelist_t is table of varchar2 (5000);
enames_with_errors namelist_t
:= namelist_t ('ABC'
, 'DEF'
, rpad ('BIGBIGGERBIGGEST', 1000, 'ABC')
, 'GHI'
);
begin
forall indx in 1 .. enames_with_errors.count save exceptions
update employees
set first_name = enames_with_errors (indx);
rollback;
exception
when others
then
dbms_output.put_line ('Updated ' || sql%rowcount || ' rows.');
dbms_output.put_line (dbms_utility.format_error_stack);
rollback;
end;
declare
e_forall_failure exception;
pragma exception_init (e_forall_failure, -24381);
type namelist_t is table of varchar2 (5000);
enames_with_errors namelist_t
:= namelist_t ('ABC',
'DEF',
rpad ('BIGBIGGERBIGGEST', 1000, 'ABC'),
'LITTLE',
rpad ('BIGBIGGERBIGGEST', 3000, 'ABC'),
'SMITHIE');
begin
forall indx in 1 .. enames_with_errors.count save exceptions
update employees
set first_name = enames_with_errors (indx);
rollback;
exception
when e_forall_failure
then
dbms_output.put_line (
'Updated ' || sql%rowcount || ' rows.');
dbms_output.put_line (sqlerrm);
for indx in 1 .. sql%bulk_exceptions.count
loop
dbms_output.put_line (
'Error '
|| indx
|| ' occurred on index '
|| sql%bulk_exceptions (indx).error_index
|| ' attempting to update name to "'
|| enames_with_errors (
sql%bulk_exceptions (indx).error_index)
|| '"');
dbms_output.put_line (
'Oracle error is '
|| sqlerrm (
-1 * sql%bulk_exceptions (indx).error_code));
end loop;
rollback;
end;
declare
e_forall_failure exception;
pragma exception_init (e_forall_failure, -24381);
type namelist_t is table of varchar2 (5000);
enames_with_errors namelist_t
:= namelist_t ('ABC',
'DEF',
rpad ('BIGBIGGERBIGGEST', 1000, 'ABC'));
begin
enames_with_errors.delete (2);
forall indx in indices of enames_with_errors save exceptions
update employees
set first_name = enames_with_errors (indx);
rollback;
exception
when e_forall_failure
then
for indx in 1 .. sql%bulk_exceptions.count
loop
dbms_output.put_line (
'Error '
|| indx
|| ' occurred on index '
|| sql%bulk_exceptions (indx).error_index
|| ' attempting to update name to "'
|| enames_with_errors (
sql%bulk_exceptions (indx).error_index)
|| '"');
end loop;
rollback;
end;
declare
e_forall_failure exception;
pragma exception_init (e_forall_failure, -24381);
type namelist_t is table of varchar2 (5000);
enames_with_errors namelist_t
:= namelist_t ('ABC',
'DEF',
rpad ('BIGBIGGERBIGGEST', 1000, 'ABC'));
function bind_array_index_for (
bind_array_in in namelist_t,
error_index_in in pls_integer,
start_in in pls_integer default null,
end_in in pls_integer default null)
return pls_integer
is
l_index pls_integer := nvl (start_in, bind_array_in.first);
begin
for indx in 1 .. error_index_in - 1
loop
l_index := bind_array_in.next (l_index);
end loop;
return l_index;
end;
begin
enames_with_errors.delete (2);
forall indx in indices of enames_with_errors save exceptions
update employees
set first_name = enames_with_errors (indx);
rollback;
exception
when e_forall_failure
then
for indx in 1 .. sql%bulk_exceptions.count
loop
dbms_output.put_line (
'Error '
|| indx
|| ' occurred on index '
|| sql%bulk_exceptions (indx).error_index
|| ' attempting to update name to "'
|| enames_with_errors (
bind_array_index_for (
enames_with_errors,
SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX))
|| '"');
end loop;
rollback;
end;