This discussion is archived
2 Replies Latest reply: Nov 21, 2012 7:51 PM by rthampi RSS

Developer 10g, COMMIT issues

rthampi Explorer
Currently Being Moderated
Hi guys
We have developed one particular application for ORACLE EBS R12 Order Management using Oracle public APIs. This custom application saves the user fed data into local tables first and then calls database level packages and procedures to facilitate Order Booking.
We are using this application from last one year, and throughout the year we have created 10,000+ transactions. Unfortunately, while reconciling the transactions we found four transactions where missing from the custom application tables, however the Order Management side tables populated with the same missing information.

Order booking is done by a dedicated button click and the sample coding is something like following for WHEN-BUTTON-PRESSED
Begin
Commit_form;
--Call first procedure;
stage_1(:header_blk.order_number);
stage_2(:PARAMETER.STAGE_1_SUCCESS);
End;
Commit_form call is supposed to save the transaction on the data entry screen first to the custom application table(s) and then invoke the procedures as listed in the coding. However in our case, the consecutive procedures are reading the value from form (:header_blk.order_number) and gone ahead with processing the data over Order management repositories for missing transactions!!! (user's identified these were the cases when there were network failures, client systems hanging up, java crashing etc)


As an immediate resolution, we changed the coding like following
We created a view against the header table and checked whether the order_number is available in the view prior calling the procedures. Sample coding
Declare
l_trx_check VARCHAR2(1);
Begin
Commit_form;
Begin
Select 'X' into l_trx_check from order_hdr_id_v
where order_number = :header_blk.order_number;
Exception
when no_data_found then
message('Local transaction not saved. Please try again');
raise form_trigger_failure;
End;
--Call first procedure;
stage_1(:header_blk.order_number);
stage_2(:PARAMETER.STAGE_1_SUCCESS);
End;
Now my query is whether there is a better approach towards this requirement and whether depending upon a view for a cross verification is really a practical solution.

regards,

raj
  • 1. Re: Developer 10g, COMMIT issues
    Andreas Weiden Guru
    Currently Being Moderated
    I would check if you could use a POST instead of a COMMIT_FORM, so that all your actions are done in the same transaction. With your coding there is the chance that the first commit is excuted, but then the form craches in the afterwards processing.
  • 2. Re: Developer 10g, COMMIT issues
    rthampi Explorer
    Currently Being Moderated
    Andreas Weiden wrote:
    I would check if you could use a POST instead of a COMMIT_FORM, so that all your actions are done in the same transaction. With your coding there is the chance that the first commit is excuted, but then the form craches in the afterwards processing.
    Thank you Andreas
    Unfortunately in our case, the exact opposite is happening. The first commit is not happening, however other commits through called procedures are happening at the database level.

    More thoughts please???

    regards,

    raj

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points