plsql - Exception handling PL SQL -
my application has following 2 procedure handles entire transaction. when nested procedure throws exception crud operation performed in outer procedure not roll back. please me re arrange or how write in better way can roll transaction.
procedure sp_send_case_next_level ( p_wfid in number, p_assessmentid in number, p_prevlevelid in number, p_apptype in number, p_sequencenumber in number, p_subsequencenumber in number, p_usercode in varchar2, p_companycode in varchar2, p_camnumber in varchar2, p_reviewtype in number, p_unittype in number, p_regioncode in number, p_casetype in varchar2, p_isapproved in varchar2, p_fromusercode in varchar2, p_errordetail out varchar2 ) v_newlevelid number(8); v_newwfid number(8); v_stepcompleted number(2); v_casecomplete varchar2(1); v_isstepcomplete varchar2(1); v_iscompanypicked varchar2(1); v_subuserinworkflow number(1); v_ispresent number(1); v_inconsistentworkflow varchar2(2); v_subusercode varchar2(8); v_errmsg varchar2(50); v_userdp number(10, 2); v_sql varchar2(2000); v_sub_seq_no number(8); v_reversalsequenceno number(4); v_reversalapptype number(2); v_newcourierid number(8); v_couriercount number(1); v_revsequenceno number(4); v_couriercomments varchar2(4000); v_required_count number(2); v_sessionpriority number(1); v_customrequiredcount number(1); begin p_errordetail := 'failure'; v_inconsistentworkflow := 'n'; v_reversalapptype := 0; v_revsequenceno :=0; v_couriercomments := ''; v_customrequiredcount := 0; savepoint start_tran; begin -- update aph_comp_leveldetails initiator of case update aph_comp_leveldetails set case_complete = 'y', end_date = sysdate, is_step_complete = 'y', is_approved = p_isapproved user_code = p_usercode , assessmentid = p_assessmentid , levelid = p_prevlevelid; -- update aph_comp_header case status update aph_comp_header set case_status_desc = 'pending', comp_reverse = 'n' assessmentid = p_assessmentid; -- update mst_tat_calc set case end time previous level user update mst_tat_calc set case_end_time = sysdate, rule_end_time = sysdate, days = fn_get_days(rule_start_time, sysdate, reason) user_code = p_usercode -- todo : removed , levelid = p_prevlevelid , assessmentid = p_assessmentid , reason <> 'c' , case_end_time null; ------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------- select count(case_complete) v_required_count aph_comp_leveldetails assessmentid = p_assessmentid , seq_no = p_sequencenumber , app_type = p_apptype , comp_reverse = 'n' , comp_withdrawn = 'n' , comp_rejected = 'n'; select count(case_complete) v_stepcompleted aph_comp_leveldetails assessmentid = p_assessmentid , seq_no = p_sequencenumber , app_type = p_apptype , case_complete = 'y' , comp_reverse = 'n' , comp_withdrawn = 'n' , comp_rejected = 'n'; begin -- aph_comp_leveldetails insert aph_comp_leveldetails (wf_id, assessmentid, levelid, user_code, case_complete, comp_fromuser, seq_no, sub_seq_no, comp_touser, start_date, is_step_complete, is_company_picked, app_type, work_flow_user) values (send.wf_id, p_assessmentid, v_newlevelid, user_on_holiday.user_code, 'n', --send.user_id, p_usercode, send.sequence_no, v_sub_seq_no, user_on_holiday.user_code, sysdate, 'n', 'n', send.app_type, send.user_id); exception when others rollback; v_errmsg := 'unable insert aph_comp_leveldetails previous level step not completed'; return; end; ------------------------------------------------------------------------------------------------- begin -- check if user alreadily part of workflow. select count(*) v_ispresent aph_wf_user assessmentid = p_assessmentid , user_id = user_on_holiday.user_code , is_wf_user = 'y'; end; ------------------------------------------------------------------------------------------------- if v_ispresent = 0 -- if user not alreadily part of workflow insert aph_wf_user. begin -- insert in aph_wf_user insert aph_wf_user (wf_id, assessmentid, user_id, sequence_no, app_type, is_editable, user_dp, unit_type, created_by, creation_date) values (v_newwfid, p_assessmentid, user_on_holiday.user_code, send.sequence_no, send.app_type, 'n', v_userdp, send.unit_type, p_usercode, sysdate); exception when others rollback; v_errmsg := 'unable insert aph_comp_leveldetails previous level step not completed'; return; end; end if; -- end of if v_ispresent = 0 ------------------------------------------------------------------------------------------------- begin --mst_tat_calc insert mst_tat_calc (assessmentid, levelid, company_code, user_code, reason, case_start_time, case_end_time, rule_start_time, rule_end_time, cam_number, review_type, unit_type, region_code, levelid_courier_to) values (p_assessmentid, /*p_prevlevelid,*/ v_newlevelid, p_companycode, /*p_usercode,*/ user_on_holiday.user_code, 'c',--this should value of enum commonactionenum.courier sysdate, null, sysdate, null, p_camnumber, p_reviewtype, p_unittype, p_regioncode, /*v_newlevelid*/ p_prevlevelid ); ------------------------------------------------------------------------------------------------- insert mst_tat_calc (assessmentid, levelid, company_code, user_code, reason, case_start_time, rule_start_time, cam_number, review_type, unit_type, region_code, days) values (p_assessmentid, v_newlevelid, p_companycode, user_on_holiday.user_code, 'n',--this should value of enum commonactionenum.normal_approval sysdate, fn_get_rule_date(sysdate), p_camnumber, p_reviewtype, p_unittype, p_regioncode, 0); exception when others rollback; v_errmsg := 'unable insert mst_tat_calc normal approver'; return; end; ------------------------------------------------------------------------------------------------- -- check if user has courier begin -- update in aph_user_courier courier status sent , sent date select count(1) v_couriercount aph_user_courier assessmentid = p_assessmentid , touser = user_on_holiday.user_code , levelid = p_prevlevelid; if v_couriercount = 0 begin pack_aph_getid.proc_aph_getid('aph_user_courier','courier_id', v_newcourierid); -- generate courier id. end; insert aph_user_courier ( courier_id, assessmentid, levelid, fromuser, touser, fromuser_comments, touser_comments, fromcourier_status, tocourier_status, from_datetime, to_datetime, documentreceived, receiveddate ) --values (v_newcourierid, p_assessmentid, v_newlevelid, p_usercode, send.user_id, null, null, 'sent' , null , sysdate , null, null, null); values ( v_newcourierid, p_assessmentid, v_newlevelid, p_usercode, user_on_holiday.user_code, v_couriercomments, null, 'sent' , null , sysdate , null, null, null ); else update aph_user_courier set fromcourier_status = 'sent', tocourier_status = null, from_datetime = sysdate assessmentid = p_assessmentid , levelid = p_prevlevelid --and touser = send.user_id; , touser = user_on_holiday.user_code end if; exception when others rollback; v_errmsg := 'unable update aph_user_courier'; return; end; begin sp_common_inserts(p_assessmentid, p_prevlevelid, v_newlevelid, 'n', 'n',0,'0','n', p_errordetail) exception when others p_errordetail := 'error in common'; rollback; end; begin update aph_wf_user set is_editable = 'n' user_id = send.user_id , assessmentid = p_assessmentid; end; end; end sp_send_case_next_level; procedure sp_common_inserts ( --p_wfid in number, p_assessmentid in number, p_prevlevelid in number, p_newlevelid in number, p_withdraw in varchar2, p_rejected in varchar2, p_prevassessmentid in number, p_borrowercode in varchar2, p_srm_lcr in varchar2, p_errordetail out varchar2 ) v_wfid number(8); v_errmsg varchar2(50); v_initiatorusercode varchar2(50); v_srno number(8); begin -- savepoint start_tran1; if p_withdraw = 'n' , p_rejected = 'n' begin -- aph_assessment_parameter insert aph_assessment_parameter ( sr_no, assessmentid, levelid, cam_date, revision_date, srm_expiry_date, part_program, product_program, product_progrm_exposure, progrm_parameters_adhered, is_separete_approver, cash_backed_exposure, exposure_guranted, clims_id, cam_login_date, borrower_rating, rating_type, rating_finalisation_date, initial_labelling_date, current_review_date, label_review_type, label, last_client_call_date, called_on_date, interface_date, comp_code ) select sr_no, p_assessmentid, p_newlevelid, cam_date, revision_date, srm_expiry_date, part_program, product_program, product_progrm_exposure, progrm_parameters_adhered, is_separete_approver, cash_backed_exposure, exposure_guranted, clims_id, cam_login_date, borrower_rating, rating_type, rating_finalisation_date, initial_labelling_date, current_review_date, label_review_type, label, last_client_call_date, called_on_date, interface_date, comp_code aph_assessment_parameter levelid = p_prevlevelid; exception when no_data_found rollback; v_errmsg := 'unable insert aph_addendum_details no data found @ previous level'; return; end; end if; if p_withdraw = 'y' or p_rejected = 'y' --aph_appraisal_details begin insert aph_appraisal_details ( assessmentid, company_code, cam_format_id, file_name, order_id, reports_html_contents, sr_no )select p_assessmentid, company_code, cam_format_id, file_name, order_id, reports_html_contents, sr_no aph_appraisal_details assessmentid = p_prevassessmentid , file_name not in (select file_name aph_ftl_details ftl_id in(29,30,31)); exception when no_data_found rollback; v_errmsg := 'unable insert aph_borrower_details no data found @ previous level'; return; end; end if; p_errordetail:='success'; exception when others rollback; p_errordetail :=v_errmsg; end sp_common_inserts; please let me know how handle exception in above 2 procedures if nested procedure sp_common_insert throws exception.
instead of using return, use raise instead. way, error encountered in sub blocks trigger exception sets value of p_errordetail in main block. thing don't have exception block in sp_send_case_next_level procedure's main block. here's revision of code:
procedure sp_send_case_next_level ( p_wfid in number, p_assessmentid in number, p_prevlevelid in number, p_apptype in number, p_sequencenumber in number, p_subsequencenumber in number, p_usercode in varchar2, p_companycode in varchar2, p_camnumber in varchar2, p_reviewtype in number, p_unittype in number, p_regioncode in number, p_casetype in varchar2, p_isapproved in varchar2, p_fromusercode in varchar2, p_errordetail out varchar2 ) v_newlevelid number(8); v_newwfid number(8); v_stepcompleted number(2); v_casecomplete varchar2(1); v_isstepcomplete varchar2(1); v_iscompanypicked varchar2(1); v_subuserinworkflow number(1); v_ispresent number(1); v_inconsistentworkflow varchar2(2); v_subusercode varchar2(8); v_errmsg varchar2(50); v_userdp number(10, 2); v_sql varchar2(2000); v_sub_seq_no number(8); v_reversalsequenceno number(4); v_reversalapptype number(2); v_newcourierid number(8); v_couriercount number(1); v_revsequenceno number(4); v_couriercomments varchar2(4000); v_required_count number(2); v_sessionpriority number(1); v_customrequiredcount number(1); begin p_errordetail := 'failure'; v_inconsistentworkflow := 'n'; v_reversalapptype := 0; v_revsequenceno :=0; v_couriercomments := ''; v_customrequiredcount := 0; savepoint start_tran; begin -- update aph_comp_leveldetails initiator of case update aph_comp_leveldetails set case_complete = 'y', end_date = sysdate, is_step_complete = 'y', is_approved = p_isapproved user_code = p_usercode , assessmentid = p_assessmentid , levelid = p_prevlevelid; -- update aph_comp_header case status update aph_comp_header set case_status_desc = 'pending', comp_reverse = 'n' assessmentid = p_assessmentid; -- update mst_tat_calc set case end time previous level user update mst_tat_calc set case_end_time = sysdate, rule_end_time = sysdate, days = fn_get_days(rule_start_time, sysdate, reason) user_code = p_usercode -- todo : removed , levelid = p_prevlevelid , assessmentid = p_assessmentid , reason <> 'c' , case_end_time null; ------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------- select count(case_complete) v_required_count aph_comp_leveldetails assessmentid = p_assessmentid , seq_no = p_sequencenumber , app_type = p_apptype , comp_reverse = 'n' , comp_withdrawn = 'n' , comp_rejected = 'n'; select count(case_complete) v_stepcompleted aph_comp_leveldetails assessmentid = p_assessmentid , seq_no = p_sequencenumber , app_type = p_apptype , case_complete = 'y' , comp_reverse = 'n' , comp_withdrawn = 'n' , comp_rejected = 'n'; begin -- aph_comp_leveldetails insert aph_comp_leveldetails (wf_id, assessmentid, levelid, user_code, case_complete, comp_fromuser, seq_no, sub_seq_no, comp_touser, start_date, is_step_complete, is_company_picked, app_type, work_flow_user) values (send.wf_id, p_assessmentid, v_newlevelid, user_on_holiday.user_code, 'n', --send.user_id, p_usercode, send.sequence_no, v_sub_seq_no, user_on_holiday.user_code, sysdate, 'n', 'n', send.app_type, send.user_id); exception when others rollback; v_errmsg := 'unable insert aph_comp_leveldetails previous level step not completed'; raise; end; ------------------------------------------------------------------------------------------------- begin -- check if user alreadily part of workflow. select count(*) v_ispresent aph_wf_user assessmentid = p_assessmentid , user_id = user_on_holiday.user_code , is_wf_user = 'y'; end; ------------------------------------------------------------------------------------------------- if v_ispresent = 0 -- if user not alreadily part of workflow insert aph_wf_user. begin -- insert in aph_wf_user insert aph_wf_user (wf_id, assessmentid, user_id, sequence_no, app_type, is_editable, user_dp, unit_type, created_by, creation_date) values (v_newwfid, p_assessmentid, user_on_holiday.user_code, send.sequence_no, send.app_type, 'n', v_userdp, send.unit_type, p_usercode, sysdate); exception when others rollback; v_errmsg := 'unable insert aph_comp_leveldetails previous level step not completed'; raise; end; end if; -- end of if v_ispresent = 0 ------------------------------------------------------------------------------------------------- begin --mst_tat_calc insert mst_tat_calc (assessmentid, levelid, company_code, user_code, reason, case_start_time, case_end_time, rule_start_time, rule_end_time, cam_number, review_type, unit_type, region_code, levelid_courier_to) values (p_assessmentid, /*p_prevlevelid,*/ v_newlevelid, p_companycode, /*p_usercode,*/ user_on_holiday.user_code, 'c',--this should value of enum commonactionenum.courier sysdate, null, sysdate, null, p_camnumber, p_reviewtype, p_unittype, p_regioncode, /*v_newlevelid*/ p_prevlevelid ); ------------------------------------------------------------------------------------------------- insert mst_tat_calc (assessmentid, levelid, company_code, user_code, reason, case_start_time, rule_start_time, cam_number, review_type, unit_type, region_code, days) values (p_assessmentid, v_newlevelid, p_companycode, user_on_holiday.user_code, 'n',--this should value of enum commonactionenum.normal_approval sysdate, fn_get_rule_date(sysdate), p_camnumber, p_reviewtype, p_unittype, p_regioncode, 0); exception when others rollback; v_errmsg := 'unable insert mst_tat_calc normal approver'; raise; end; ------------------------------------------------------------------------------------------------- -- check if user has courier begin -- update in aph_user_courier courier status sent , sent date select count(1) v_couriercount aph_user_courier assessmentid = p_assessmentid , touser = user_on_holiday.user_code , levelid = p_prevlevelid; if v_couriercount = 0 begin pack_aph_getid.proc_aph_getid('aph_user_courier','courier_id', v_newcourierid); -- generate courier id. end; insert aph_user_courier ( courier_id, assessmentid, levelid, fromuser, touser, fromuser_comments, touser_comments, fromcourier_status, tocourier_status, from_datetime, to_datetime, documentreceived, receiveddate ) --values (v_newcourierid, p_assessmentid, v_newlevelid, p_usercode, send.user_id, null, null, 'sent' , null , sysdate , null, null, null); values ( v_newcourierid, p_assessmentid, v_newlevelid, p_usercode, user_on_holiday.user_code, v_couriercomments, null, 'sent' , null , sysdate , null, null, null ); else update aph_user_courier set fromcourier_status = 'sent', tocourier_status = null, from_datetime = sysdate assessmentid = p_assessmentid , levelid = p_prevlevelid --and touser = send.user_id; , touser = user_on_holiday.user_code end if; exception when others rollback; v_errmsg := 'unable update aph_user_courier'; raise; end; begin sp_common_inserts(p_assessmentid, p_prevlevelid, v_newlevelid, 'n', 'n',0,'0','n', p_errordetail) exception when others p_errordetail := 'error in common'; rollback; end; begin update aph_wf_user set is_editable = 'n' user_id = send.user_id , assessmentid = p_assessmentid; end; end; exception when others rollback; p_errordetail :=v_errmsg; end sp_send_case_next_level; procedure sp_common_inserts ( --p_wfid in number, p_assessmentid in number, p_prevlevelid in number, p_newlevelid in number, p_withdraw in varchar2, p_rejected in varchar2, p_prevassessmentid in number, p_borrowercode in varchar2, p_srm_lcr in varchar2, p_errordetail out varchar2 ) v_wfid number(8); v_errmsg varchar2(50); v_initiatorusercode varchar2(50); v_srno number(8); begin -- savepoint start_tran1; if p_withdraw = 'n' , p_rejected = 'n' begin -- aph_assessment_parameter insert aph_assessment_parameter ( sr_no, assessmentid, levelid, cam_date, revision_date, srm_expiry_date, part_program, product_program, product_progrm_exposure, progrm_parameters_adhered, is_separete_approver, cash_backed_exposure, exposure_guranted, clims_id, cam_login_date, borrower_rating, rating_type, rating_finalisation_date, initial_labelling_date, current_review_date, label_review_type, label, last_client_call_date, called_on_date, interface_date, comp_code ) select sr_no, p_assessmentid, p_newlevelid, cam_date, revision_date, srm_expiry_date, part_program, product_program, product_progrm_exposure, progrm_parameters_adhered, is_separete_approver, cash_backed_exposure, exposure_guranted, clims_id, cam_login_date, borrower_rating, rating_type, rating_finalisation_date, initial_labelling_date, current_review_date, label_review_type, label, last_client_call_date, called_on_date, interface_date, comp_code aph_assessment_parameter levelid = p_prevlevelid; exception when no_data_found rollback; v_errmsg := 'unable insert aph_addendum_details no data found @ previous level'; raise; end; end if; if p_withdraw = 'y' or p_rejected = 'y' --aph_appraisal_details begin insert aph_appraisal_details ( assessmentid, company_code, cam_format_id, file_name, order_id, reports_html_contents, sr_no )select p_assessmentid, company_code, cam_format_id, file_name, order_id, reports_html_contents, sr_no aph_appraisal_details assessmentid = p_prevassessmentid , file_name not in (select file_name aph_ftl_details ftl_id in(29,30,31)); exception when no_data_found rollback; v_errmsg := 'unable insert aph_borrower_details no data found @ previous level'; raise; end; end if; p_errordetail:='success'; exception when others rollback; p_errordetail :=v_errmsg; end sp_common_inserts;
Comments
Post a Comment