error in save exception
813348Dec 29 2010 — edited Dec 29 2010hii all
i was trying to undersatand the concept of save exception using bulk collect i cant figure it y it is not working i m using hr schema
so i started by creating a table tab1
create table tab1 as select employee_id, first_name
from employees
then
i was manually deleting a row from collection i.e 20th row and wanted to save exception in save exception and wanted that the work should continue even though there was an exception as what save exception does but it moves out as soon as exception occurs which normally "forall" does but i dont know why save exception is not working
declare
type t1 is table of number;
vtt1 t1;
type t2 is table of employees.first_name%type;
vt2 t2;
n1 number;
exp exception;
pragma exception_init(exp,-22160);
begin
select employee_id
bulk collect into vtt1
from tab1;
vtt1.delete(20);
forall i in vtt1.first..vtt1.last save exceptions
delete from tab1
where employee_id=vtt1(i)
returning first_name bulk collect into vt2;
exception
when exp then
n1 := sql%bulk_exceptions.count;
for i in 1..n1 loop
dbms_output.put_line('here is the exception at '||sql%bulk_exceptions(i).error_index||' code '||sql%bulk_exceptions(i).error_code);
end loop;
end;
actually when it reaches 2oth row it does not save exception it moves out and goes to exception section i dont know why