Rewrite query using bulk collect to improve performance | Newbie | | Join Date: Mar 2008
Posts: 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.' -
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,
|  | Expert | | Join Date: Apr 2007
Posts: 141
| | | re: Rewrite query using bulk collect to improve performance
handle the error using exception block.
| | Newbie | | Join Date: Mar 2008
Posts: 3
| | | re: Rewrite query using bulk collect to improve performance
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
|  | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Rewrite query using bulk collect to improve performance
Oravm,
Please remember to use [code] tags when ever you post any source code in forum.
For more details on code tags, check here |  |
Similar Oracle Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,439 network members.
|
