If you've decided to stick with INSERTs you can prevent insertion of duplicate rows by using constraints whether it primary key or unique key. If it happens to violate a unique constraint your script will stop and you'll have to roollback all changes made by previous inserts(unless you have committed every single of them). To handle that exception you could write a similar pls/sql block.
declare
l_unique_exception exception;
pragma exception_init(l_unique_exception, -1);
begin
insert into test(id, test_vector)
values(1, 123);
insert into test(id, test_vector)
values(1, 123);
......
Insert into
commit;
exception
when l_unique_exception
then process the exception;
end;
IN ADDITION
If you want to proceed after one of the inserts raises an exception then the following example might be in handy.
Create a table that going to contain errors. For example.
CREATE TABLE tb_errors (
ErrorTag varchar2(123)
)
Provide an error logging invoking CREATE_ERROR_LOG procedure of DBMS_ERRLOG package
DBMS_ERRLOG.CREATE_ERROR_LOG('YourDmlTable. Test in this case', 'tb_errors');
Add log errors into clause to each insert
Here is an example
declare
begin
insert into test(id, col1)
values(1, 123)
log errors into tb_errors('simple expression') reject limit unlimited;
insert into test(id, col1)
values(1, 123)
log errors into tb_errors('simple expression') reject limit unlimited;
insert into test(id, col1)
values(1, 123)
log errors into tb_errors('simple expression') reject limit unlimited;
commit;
end;
After your script is completed you can query error logging table, tb_errors in this case, to see what went wrong.
