8 Replies Latest reply: Nov 6, 2012 9:27 AM by WJHORA RSS

    Help: commit_form runs twice on exception in DB

    WJHORA
      I have a form with data block backed by stored procedures. The update procedure in DB end up with an exception due to data error during do_key('commit_form'). The on-error trigger catches the error and brings up the alert.
      declare
        alert_button number;
      begin
        set_alert_property('ok_alert', alert_message_text, DBMS_ERROR_TEXT);
        alert_button := show_alert('ok_alert');
        raise form_trigger_failure;
      end;
      However, after clicking OK, another commit dialogue (Yes, No Cancel) comes out ask "Do you want to save the changes you made?" It is like Oracle default message.
      Inside on-commit trigger, I have a customized message like "Do you want to save the reschedule information?"

      What I really want is after OK Alert, the forms goes back like "Cancel."

      Any suggestions are greatly appreciated.

      Thanks in advance.
      Jimmy
        • 1. Re: Help: commit_form runs twice on exception in DB
          user346369
          Do you have any code in your Key-Commit trigger? If so, what is it?

          What code do you have following the do_key('commit_form')?

          After a Commit_Form; or the do_key method, you should always follow that command with this:
            COMMIT_FORM;
            IF :SYSTEM.FORM_STATUS <> 'QUERY'
            OR NOT FORM_SUCCESS  THEN
              RAISE FORM_TRIGGER_FAILURE;
            END IF;
          • 2. Re: Help: commit_form runs twice on exception in DB
            WJHORA
            Thanks Steve. This is the code in the form level key-commit trigger (see the comment: -- the error is caused at this commit_form):
            declare
              alert_button number;
            begin
              if :system.form_status = 'CHANGED' then
                alert_button := show_alert('ync_alert');
                if alert_button = alert_button1 then
                  -- the error is caused at this commit_form;
                  commit_form;
                  go_block('waiting');
                  execute_query;
                  last_record;
                  :control.c_select := 'N';
                  :control.i_tot := :system.cursor_record;
                  :control.i_sel := 0;
                  :control.i_sel_disp := :control.i_sel||'/'||:control.i_tot;
                  if :parameter.p_upt_flg = 'U' then
                    go_record(:control.top_rec);
                    go_record(:control.cur_rec);
                  else
                    first_record;
                  end if;
                elsif alert_button = alert_button2 then
                  clear_form(no_validate);
                  go_block('waiting');
                  execute_query;
                  last_record;
                  :control.c_select := 'N';
                  :control.i_tot := :system.cursor_record;
                  :control.i_sel := 0;
                  :control.i_sel_disp := :control.i_sel||'/'||:control.i_tot;
                  if :parameter.p_upt_flg = 'U' then
                    go_record(:control.top_rec);
                    go_record(:control.cur_rec);
                  else
                    first_record;
                  end if;
                else
                  null;
                end if;
              else
                raise form_trigger_failure;
              end if;
            end;
            Edited by: WJHORA on Nov 2, 2012 12:53 PM
            • 3. Re: Help: commit_form runs twice on exception in DB
              WJHORA
              I just checked. It is this block of code after commit_form causing the problem:
              go_block('waiting');
              execute_query;
              last_record;
              :control.c_select := 'N';
              :control.i_tot := :system.cursor_record;
              :control.i_sel := 0;
              :control.i_sel_disp := :control.i_sel||'/'||:control.i_tot;
              first_record;
              If no exception, the on-error trigger will not be triggered and the code will work nicely. However, once the DB error triggered the on-error trigger, the block of the code above brings up the second alert: "Do you want to save the changes..."
              My question is then two folds:
              1. When confirm_form successfully, the block of code will run to refresh the data block.
              2. When on-error trigger is triggered, the block of code will not run so that the second commit will not happen.

              Any suggestions are greatly appreciated.

              Thanks.
              Jimmy
              • 4. Re: Help: commit_form runs twice on exception in DB
                MLBrown
                Did you insert the if-then logic that Steve mentions?

                Between the "commit_form;" and the "go_block('waiting');" commands in the key-commit trigger add the if-then logic that Steve listed.

                If your form status is still in a CHANGED status, which it would be if your commit failed, then as soon as you try to issue the EXECUTE_QUERY; forms is going to prompt the user to save their changes unless you do something like a CLEAR_FORM(NO_VALIDATE); before the EXECUTE_QUERY;
                • 5. Re: Help: commit_form runs twice on exception in DB
                  WJHORA
                  Thanks MLBrown. It is all set by adding
                  CLEAR_FORM(NO_VALIDATE); right after commit_form.

                  Have a nice day.
                  Jimmy
                  • 6. Re: Help: commit_form runs twice on exception in DB
                    user346369
                    So, let me get this straight... Your user presses a button that fires off a procedure. If the procedure fails and produces an error message, you want to clear the user's work and re-query? The user doesn't get a chance to fix the problem and re-try the process???

                    That does not sound like a good approach to me.
                    • 7. Re: Help: commit_form runs twice on exception in DB
                      MLBrown
                      I agree with Steve. I think you would be better off putting in the code he mentioned between the "commit_form;" and the "go_block('waiting');" commands. That way if the commit failed you would abort the process. Depending on what the problem was that caused the problem though needs to be addressed and a message should be displayed to the user so they can fix it.
                      • 8. Re: Help: commit_form runs twice on exception in DB
                        WJHORA
                        Hi Steve,

                        The form is designed for a request approval or reject process. The commit_form handles both. The actual DB procedure does update and insert on multiple tables. The failure of the approval causing the alert.The alert tells the user the problem and user will do reject and make date fix through another form.

                        Thank you for raising the question and I hope this made it clear. In fact, all these came from your first suggestion.

                        Regards,

                        Jimmy