You Asked
pkg_ic_efile_components.sp_ic_account_open_txn_info(v_work_ref.id_acc,p_interval_id,p_mem_cur);
LOOP
BEGIN
fetch p_mem_cur into v_memo;
exit when p_mem_cur%notfound;
IF v_memo.memo_date < to_date(sysdate) THEN
v_past_dues:=v_past_dues+v_memo.memo_amount;
END IF;
IF v_memo.memo_type IN ('OpenCreditNotes','OpenPayments') THEN
v_feature_name:=v_memo.memo_type||': '||v_memo.memo_no||'- '||v_memo.feature_desc;
/* v_feature_charge:=v_memo.memo_amount; */
INSERT INTO t_ic_efeed_conf_format2
(
job_id,
jobsub_type,
jobfilename,
sort_level,
company_name,
company_no,
account_no,
confid,
conf_date,
pac,
feature_charge,
excise_tax,
surcharge_tax,
telecom_tax,
total
)
VALUES
(
v_work_ref.jobid,
v_work_ref.jobsubtype,
v_work_ref.jobfilename,
15,
v_company,
v_comp_no,
v_acc_no,
'Open Credits/Debits : ' || v_feature_name,
v_memo.memo_date,
' ',
v_feature_charge,
v_conf.excise_tax,
v_conf.ucc_charges,
0,
nvl(v_memo.memo_amount,0)
);
End if;
EXCEPTION
WHEN others then
v_err_code:=SQLCODE;
v_err_msg:=SQLERRM;
INSERT INTO SWN01ZPAPP14.T_RECEVENT_RUN_DETAILS
(
ID_DETAIL,
ID_RUN,
TX_TYPE,
TX_DETAIL,
DT_CRT
)
VALUES
(
SWN01ZPAPP14.SEQ_T_RECEVENT_RUN_DETAILS.NEXTVAL,
p_id_run,
'Warning',
'SP_IC_EFEED_CONF_FORMAT2: Error Code - '||'-'||v_err_code || ' - ' || v_err_msg || ': '|| 'Job Id-'||
v_work_ref.jobid||' ID ACC- '|| v_work_ref.id_acc || ' Res Id-'||v_conf.res_id ||' Line Number-'||dbms_utility.format_error_backtrace,
SWN01ZPAPP14.GETUTCDATE()
);
COMMIT;
END ;
End Loop;
It gives "fetch out of sequence" error.
Please advise.
Thanks
Vishal
and Tom said...
missing a bit of code, I'll guess
You have a select for update cursor.
You commit in your loop when you hit an error.
When you commit a select for update, it loses it's locks, hence we close the cursor.
Looks like you want to use DML ERROR LOGGING. it'll save the failed record AND the error codes for you automagically. You need do nothing.
You could write that code as a SINGLE SQL STATEMENT - no procedural code at all.
short of that, change your insert to use "log errors" and have it log the failure for you - it'll commit that error logged record and you won't have to do it and your code won't fail anymore.
http://asktom.oracle.com/Misc/how-cool-is-this.html
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment