Dear all,
I am currently writing package in plsql.
The main logic of the program is as follow .. Program works fine .. but ... when no data is found for current V_1, V_2,V_3 , insertion is done with previous cursor row value of V_1,V_2,V_3, ... which is not good.
I tried to change the last nested cursor with first..last instead of 1..count, but result is the same.
Any idea?
open c_trt;
loop
fetch c_trt bulk collect into bk_trig limit v_limit;
open c_bkeve;
fetch c_bkeve bulk collect into bk_eve limit v_limit;
if bk_eve.count > 0 then
for k in 1..bk_eve.count loop;
case
when a =1 then
open c_bkieve(bk_eve(k).age,bk_eve(k).ope, bk_eve(k).eve);
fetch c_bkieve bulk collect into bk_ieve limit v_limit;
if bk_ieve.count > 0 then
for j in 1..bk_ieve.count loop
fetch c_bkieve bulk collect into bk_ieve limit v_limit;
if bk_ieve.count > 0 then
for j in 1..bk_ieve.count loop
case bk_ieve(j).a
when 'ABC' then
V_1 := nvl(trim(bk_ieve(j).b),null);
when 'XYZ' then
V_2 := nvl(trim(substr(bk_ieve(j).b,1,4)),null);
V_3 := nvl(trim(substr(bk_ieve(j).b,6,22)),null);
else
null;
end case;
end loop;
else
V_1 := null;
V_2 := null;
V_3 := null;
end if;
close c_bkieve;
insert into xxx values(V_1,V_2,V_3);
etc, etc
Thanks for your help
Jerome