Tuesday 7 August 2012

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.