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

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_code, p.a_amt_recd, p.x_deduction_month,p.x_nric_old,a.angkasa_ic,a.type 
into l_angkasa
from pp_angkasa_receipt_dets p 
JOIN angkasa a
on p.c_client_code = a.angkasa_ic
--where c_angkasa_ref_no = ref_no
where c_angkasa_ref_no = '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_month,x_nric_old
from pp_angkasa_receipt_dets 
where c_angkasa_ref_no = 'BP22868';
 
TYPE cur_angkasa_type IS TABLE OF cur_angkasa%ROWTYPE; 
rec1 cur_angkasa_type; 
recl cur_angkasa%ROWTYPE; 
 
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,type--error line51
into v_cntr_code, v_receipt_code 
from angkasa 
where angkasa_ic = recl.c_client_code 
and sequence_no = recl.n_seq_no; 
 
Exit when cur_angkasa%notfound; 
Dbms_output.put_line ('End of the Loop');
 
if nvl(recl.a_amt_recd,0) <> 0 then 
insert into receipt_info_all(c_cntr_code,c_receipt_no,d_receipt_date,a_receipt_amt,x_receipt_type,x_lastupd_user,x_lastupd_prog,x_lastupd_inftim)
values (v_cntr_code,receipt_no,receipt_date,recl.a_amt_recd,v_receipt_code,user,'PREM_PROC',to_char(get_control_date,'dd/mm/rr hh24:mi:ss')); 
end if; 
 
end loop;
end;
 

/*---------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.PROCESS_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.

 
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,TYPE
              INTO v_cntr_code, v_receipt_code 
              FROM angkasa 
              WHERE angkasa_ic = rec1.c_client_code 
               AND sequence_no = rec1.n_seq_no; 
 
IF NVL(recl.a_amt_recd,0) <> 0 Then 
            INSERT INTO receipt_info_all(c_cntr_code,c_receipt_no,d_receip    t_date,a_receipt_amt,x_receipt_type,x_lastupd_user  ,x_lastupd_prog,x_lastupd_inftim)
        VALUES (v_cntr_code,receipt_no,receipt_date,rec1.a_amt_re   cd,v_receipt_code,USER,'PREM_PROC',TO_CHAR(get_con  trol_date,'dd/mm/rr hh24:mi:ss')); 
      END IF; 
     End Loop;
 EXIT WHEN cur_angkasa%notfound;
END LOOP;
exception
 WHEN OTHERS THEN
        dbms_output.put_line(SQLCODE || SQLERRM);
END;
 
 

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