More ways to prevent a single unusable value breaking your entire statement SQL

 


create table so_we_can_keep_the_good_stuff (and_process_the_bad_later integer primary key);

create procedure process_the_failed_elements as
begin
dbms_output.put_line('Number of errors is ' || sql%bulk_exceptions.count );
for i in 1 .. sql%bulk_exceptions.count loop

dbms_output.put_line ('Error ' || i || ' occurred during '|| 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Oracle error is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));

end loop;
end;
/

set serveroutput on
declare
type arr is table of number;
my_array arr := arr(1, 1, 2, 3, 3, 4); -- note the duplicates

there_was_a_problem EXCEPTION;
PRAGMA EXCEPTION_INIT(there_was_a_problem, -24381);

begin

forall dodgy_data in my_array.first .. my_array.last save exceptions
insert into so_we_can_keep_the_good_stuff (
and_process_the_bad_later
) values (
my_array(dodgy_data)
);

exception
when there_was_a_problem then
process_the_failed_elements;

end;
/

select * from so_we_can_keep_the_good_stuff;

drop table so_we_can_keep_the_good_stuff purge;
drop procedure process_the_failed_elements;

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)