|
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.' -
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,
| |
Share this Question
| Expert 100+
P: 145
|
handle the error using exception block.
| |
|
P: 3
|
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
| |
| 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 | | Post your reply Help answer this question
Didn't find the answer to your Oracle Database question?
| |  Question stats - viewed: 1701
- replies: 3
- date asked: Mar 26 '08
|
