Monday 22 April 2013

how to handle errors while doing bulk data uploading in through oracle pl sql procedure how to handle errors while porting bulk data in oracle pl sql:


how to handle errors while doing bulk data uploading in through oracle pl sql procedure
how to handle errors while porting bulk data in oracle pl sql:
if any record is rejected also i need to complete the remaining records without stoping the procedure execution

declare
--variable declaration;
begin
for c in (select statement)
loop
begin
-------------
---------
execute any procedures
 or any dml statements
-------------
-------------
exception 
---handle exceptions(i.e userdefined or predefined);
insert into error_log_temp(col1,col2,...)values(value1,value2,....);
end;
end loop;
end;

example:
================

create procedure p1 
is
v_error_no varchar2(100);
begin
for c1 in (select empno,ename,sal from emp)
loop
begin
insert into emp_dup (empno,ename,sal) values(c1.empno,c1.ename,c1.sal);
exception when others then
v_error_no :=sqlerrm;
insert into emp_error_log(error_name,empno,ename) values(v_error_no,c1.empno,c1.ename);
end;
end loop;
end p1;

in above insert statement if it gives any errors we are trapping in exception block and inserting into
error log table with out stopping the procedure execution.

No comments:

Post a Comment