Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

error in save exception

813348Dec 29 2010 — edited Dec 29 2010
hii 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
This post has been answered by Saubhik on Dec 29 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 26 2011
Added on Dec 29 2010
2 comments
144 views