Rewrite query using bulk collect to improve performance

[oravm]

Hi,

I re-write a query and used bulk collect to improve the performance of the batch process. The query below has NO compile error but when execute query there is error 'ORA-01403: no data found.'

[code=oracle]
CREATE OR REPLACE PROCEDURE PROCESS_ANGKASA (REF_NO varchar2)is
v_cntr_code varchar2(16);
v_receipt_code varchar2(3);
start_time number;
end_time number;
receipt_date date;
receipt_no varchar2(50);
c_receipt_no varchar2(30);

--JOIN IN CURSOR
/* cursor cur_angkasa(ref _no varchar2) is
select p.c_client_code , p.n_seq_no, x_employer_no, p.x_employee_no ,
p.x_receipt_cod e, p.a_amt_recd, p.x_deduction_m onth,p.x_nric_o ld,a.angkasa_ic ,a.type
into l_angkasa
from pp_angkasa_rece ipt_dets p
JOIN angkasa a
on p.c_client_code = a.angkasa_ic
--where c_angkasa_ref_n o = ref_no
where c_angkasa_ref_n o = 'BP22868'
and p.n_seq_no = a.sequence_no; */

cursor cur_angkasa(ref _no varchar2) is
select c_client_code, n_seq_no, x_employer_no, x_employee_no,
x_receipt_code, a_amt_recd, x_deduction_mon th,x_nric_old
from pp_angkasa_rece ipt_dets
where c_angkasa_ref_n o = 'BP22868';

TYPE cur_angkasa_typ e IS TABLE OF cur_angkasa%ROW TYPE;
rec1 cur_angkasa_typ e;
recl cur_angkasa%ROW TYPE;

Begin

receipt_date := sysdate;
receipt_no :='HQ1234';

Open cur_angkasa(ref _no);
DBMS_OUTPUT.PUT _LINE('TESTING BULK COLLECT');
Fetch cur_angkasa bulk collect into rec1 limit 100;
For i in 1..rec1.count
loop
Dbms_output.put _line('In the Loop');
Dbms_output.put _line (recl.c_client_ code); -- no data
select c_cntr_code,typ e--error line51
into v_cntr_code, v_receipt_code
from angkasa
where angkasa_ic = recl.c_client_c ode
and sequence_no = recl.n_seq_no;

Exit when cur_angkasa%not found;
Dbms_output.put _line ('End of the Loop');

if nvl(recl.a_amt_ recd,0) <> 0 then
insert into receipt_info_al l(c_cntr_code,c _receipt_no,d_r eceipt_date,a_r eceipt_amt,x_re ceipt_type,x_la stupd_user,x_la stupd_prog,x_la stupd_inftim)
values (v_cntr_code,re ceipt_no,receip t_date,recl.a_a mt_recd,v_recei pt_code,user,'P REM_PROC',to_ch ar(get_control_ date,'dd/mm/rr hh24:mi:ss'));
end if;

end loop;
end;
[/code]


/*---------RESULTS--------- */
14:00:05 SQL> exec PROCESS_ANGKASA ('BP22868');
TESTING BULK COLLECT
In the Loop
begin PROCESS_ANGKASA ('BP22868'); end;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "PRODUCTION.PRO CESS_ANGKASA", line 51
ORA-06512: at line 1


real: 94
14:00:06 SQL>

*END ---------ERROR --------- */

Please guide me to resolve the above problem.

Thanks & Regards,

[Saii]

handle the error using exception block.

[oravm]

Thanks Saii,

Added exception .. Below is the changes.
[code=oracle]

Begin
Open cur_angkasa(ref _no);
Loop
Fetch cur_angkasa BULK COLLECT into rec1 limit 100;
For i in 1..rec1.count
Loop
SELECT c_cntr_code,TYP E
INTO v_cntr_code, v_receipt_code
FROM angkasa
WHERE angkasa_ic = rec1.c_client_c ode
AND sequence_no = rec1.n_seq_no;

IF NVL(recl.a_amt_ recd,0) <> 0 Then
INSERT INTO receipt_info_al l(c_cntr_code,c _receipt_no,d_r eceip t_date,a_receip t_amt,x_receipt _type,x_lastupd _user ,x_lastupd_prog ,x_lastupd_inft im)
VALUES (v_cntr_code,re ceipt_no,receip t_date,rec1.a_a mt_re cd,v_receipt_co de,USER,'PREM_P ROC',TO_CHAR(ge t_con trol_date,'dd/mm/rr hh24:mi:ss'));
END IF;
End Loop;
EXIT WHEN cur_angkasa%not found;
END LOOP;
exception
WHEN OTHERS THEN
dbms_output.put _line(SQLCODE || SQLERRM);
END;

[/code]

regards,
Vijaya

[amitpatel66]

Oravm,

Please remember to use [CODE] tags when ever you post any source code in forum.

For more details on code tags, check here