4 Replies Latest reply: Mar 11, 2013 10:49 PM by rthampi RSS

    Calling database level procedures after a commit

    rthampi
      Hi guys

      We experienced a kind of issue with COMMIT and calling database level procedures after the COMMIT with one of the in-house built applications. This particular application calls a number of database level procedures (ORACLE APIs EBS 12.0.4/12.0.6) immediately once after the data is inserted into the custom application tables. For example

      The button click procedure is as listed below

      Commit;
      do_this;
      do_that;

      Out of 40,000 transactions we made, around a dozen transactions only executed the procedures, completely failing to commit the data to custom tables. Now, I would like to know how I can address this situation better. I must make sure that the custom application tables have relevant data prior calling the do_this; do_that procedures.

      What I tried to implement was to do a record count from the custom application tables, and if the count is available then only call the procedures. However I believe there should be a better mechanism :O

      Any suggestions?

      Thanks


      raj
        • 1. Re: Calling database level procedures after a commit
          InoL
          What are you committing exactly? Is your form based on database tables?
          completely failing to commit the data to custom tables
          I've never heard of failing commit (or commit_form). So, it's more likely that you either
          - thought you had database changes to commit, but there aren't any. Do you see a "no changes to commit" message?
          - you have a wrong exception handler somewhere (like: "when others then null").
          - you have an on-error procedure that doesn't show you the error.
          • 2. Re: Calling database level procedures after a commit
            user346369
            Commit;
            do_this;
            do_that;
            If you read the Forms online help on the Form_Success Built-In as well as the example in the Commit_Form Built-in, you will see that you should always test Form_Status as well as Form_Success after a commit_form call (Commit within Forms is the same as Commit_Form).

            From online help:
            FORM_SUCCESS should not be used to test whether a COMMIT_FORM or POST Built-in has succeeded. Because COMMIT_FORM may cause many other triggers to fire, when you evaluate FORM_SUCCESS it may not reflect the status of COMMIT_FORM but of some other, more recently executed built-in. A more accurate technique is to check that the SYSTEM.FORM_STATUS variable is set to QUERY after the operation is done.
            Since your code does not check Form_Status and Form_Success, it is likely that the Commit_Form failed, but your do_this and do_that procedures ran anyway.

            This code is standard in all of the forms running here:
              COMMIT_FORM;
              IF :SYSTEM.FORM_STATUS <> 'QUERY'
              OR NOT FORM_SUCCESS  THEN
                RAISE FORM_TRIGGER_FAILURE;
              END IF;
            Edit: Andreas' recommendation below is the best solution!

            Edited by: Steve Cosner on Mar 11, 2013 1:40 PM
            • 3. Re: Calling database level procedures after a commit
              Andreas Weiden
              If you want to have all that in one transaction, use a POST-FORMS-COMMIT-trigger. At this point, forms has applied all changes to the db, but not yet issued the final commit.
              • 4. Re: Calling database level procedures after a commit
                rthampi
                Thank you guys for the replies. I agree to the point that, COMMIT FORM failure as once in a blue moon wonder.
                Anyway, the called database level procedures must return few value to the calling form after the execution, say Order Number for a Sales Order. That's why the commit was stitched to a button, followed by calls to procedures, for a better control. I did use form_success, however never thought of checking the STATUS of forms.

                Thanks guys once again for your thoughts and suggestions.

                regards,

                raj

                Edited by: rthampi on Mar 11, 2013 8:45 PM

                Edited by: rthampi on Mar 11, 2013 8:45 PM