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.
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