364,088 Members | 5397 Browsing Online
Community for Developers & IT Professionals
Bytes IT Community

Rewrite query using bulk collect to improve performance

oravm
P: 3
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.'

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PROCEDURE PROCESS_ANGKASA(REF_NO varchar2)is
  2. v_cntr_code varchar2(16);
  3. v_receipt_code varchar2(3);
  4. start_time number;
  5. end_time number; 
  6. receipt_date date; 
  7. receipt_no varchar2(50);
  8. c_receipt_no varchar2(30);
  9.  
  10. --JOIN IN CURSOR
  11. /* cursor cur_angkasa(ref_no varchar2) is 
  12. select p.c_client_code, p.n_seq_no, x_employer_no, p.x_employee_no,
  13. p.x_receipt_code, p.a_amt_recd, p.x_deduction_month,p.x_nric_old,a.angkasa_ic,a.type 
  14. into l_angkasa
  15. from pp_angkasa_receipt_dets p 
  16. JOIN angkasa a
  17. on p.c_client_code = a.angkasa_ic
  18. --where c_angkasa_ref_no = ref_no
  19. where c_angkasa_ref_no = 'BP22868'
  20. and p.n_seq_no = a.sequence_no; */
  21.  
  22. cursor cur_angkasa(ref_no varchar2) is 
  23. select c_client_code, n_seq_no, x_employer_no, x_employee_no, 
  24. x_receipt_code, a_amt_recd, x_deduction_month,x_nric_old
  25. from pp_angkasa_receipt_dets 
  26. where c_angkasa_ref_no = 'BP22868';
  27.  
  28. TYPE cur_angkasa_type IS TABLE OF cur_angkasa%ROWTYPE; 
  29. rec1 cur_angkasa_type; 
  30. recl cur_angkasa%ROWTYPE; 
  31.  
  32. Begin 
  33.  
  34. receipt_date := sysdate;
  35. receipt_no :='HQ1234'; 
  36.  
  37. Open cur_angkasa(ref_no); 
  38. DBMS_OUTPUT.PUT_LINE('TESTING BULK COLLECT'); 
  39. Fetch cur_angkasa bulk collect into rec1 limit 100; 
  40. For i in 1..rec1.count 
  41. loop 
  42. Dbms_output.put_line('In the Loop'); 
  43. Dbms_output.put_line (recl.c_client_code); -- no data 
  44. select c_cntr_code,type--error line51
  45. into v_cntr_code, v_receipt_code 
  46. from angkasa 
  47. where angkasa_ic = recl.c_client_code 
  48. and sequence_no = recl.n_seq_no; 
  49.  
  50. Exit when cur_angkasa%notfound; 
  51. Dbms_output.put_line ('End of the Loop');
  52.  
  53. if nvl(recl.a_amt_recd,0) <> 0 then 
  54. 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)
  55. 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')); 
  56. end if; 
  57.  
  58. end loop;
  59. end;
  60.  

/*---------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,
Mar 26 '08 #1
Share this Question
Share on Google+
3 Replies


Saii
Expert 100+
P: 145
handle the error using exception block.
Mar 26 '08 #2

oravm
P: 3
Thanks Saii,

Added exception .. Below is the changes.
Expand|Select|Wrap|Line Numbers
  1.  
  2. Begin 
  3.     Open cur_angkasa(ref_no); 
  4.     Loop
  5.     Fetch cur_angkasa BULK COLLECT into rec1 limit 100; 
  6.       For i in 1..rec1.count
  7.         Loop
  8.              SELECT c_cntr_code,TYPE
  9.               INTO v_cntr_code, v_receipt_code 
  10.               FROM angkasa 
  11.               WHERE angkasa_ic = rec1.c_client_code 
  12.                AND sequence_no = rec1.n_seq_no; 
  13.  
  14. IF NVL(recl.a_amt_recd,0) <> 0 Then 
  15.             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)
  16.         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')); 
  17.       END IF; 
  18.      End Loop;
  19.  EXIT WHEN cur_angkasa%notfound;
  20. END LOOP;
  21. exception
  22.  WHEN OTHERS THEN
  23.         dbms_output.put_line(SQLCODE || SQLERRM);
  24. END;
  25.  
  26.  
regards,
Vijaya
Mar 29 '08 #3

amitpatel66
Expert 100+
P: 2,072
Oravm,

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

For more details on code tags, check here
Mar 29 '08 #4

Post your reply

Help answer this question



Didn't find the answer to your Oracle Database question?

You can also browse similar questions: Oracle Database