update when dup_val_on_index using bulk
772329Jun 8 2010 — edited Aug 17 2011Hi,
My goal is to update table data when insert fails on PK. HOW TO GET VALUE OF bulk collection beyond insert.. how else can i write this ?
create or replace procedure test
cursor x is select * from emp;
TYPE empno_typ IS TABLE OF NUMBER(30) INDEX BY PLS_INTEGER;
TYPE ename_typ IS TABLE OF varchar2(50) INDEX BY PLS_INTEGER;
TYPE sal_TYP IS TABLE OF number(15,2) INDEX BY PLS_INTEGER;
empno_tab empno_typ ;
ename_tab ename_typ;
sal_tab sal_typ;
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
begin
open x;
loop
fetch x bulk collect into empno_tab, ename_tab, sal_tab limit 1000;
forall i in 1 .. empno_tab.count save exceptions
insert into emp (emp_no, ename, esal) values(empno_tab(i), ename_tab(i), sal_tab(i));
if sql%rowcount = 0 then
update x set empno = empno_tab(i),
ename = ename_tab(i),
esal = sal_tab(i);
end if;
end loop;
close x;
exception
when dml_errors then
null;
end;
/