9 Replies Latest reply on May 22, 2013 7:54 PM by CraigB

    Error Handling in Forms

    Maahjoor
      Dear all,
      we are using oracle developer suite 10g r2 with oracle database 10g r2 against windows server 2003.
      Error handling is a very big issue.
      i try to do the following, just correct me, or make the trigger more efficient.
      i face unexpected messages, like a message two time displayed, sometime important message never displayed.
      i just set the :system.message_level =20 in the when-new-form-instance trigger.
      and create on-error trigger like below
      declare
      errcode number:=error_code;dbmserrcode number;dbmserrtext varchar2(200);
      formtxt varchar2(200);
      errtype varchar2(3);
      begin
      
      
      --------- user defined database exceptions errors raised by --RAISE_APPLICATION_ERROR
      
      IF DBMS_ERROR_CODE in (-20738,.......) then
      raise form_trigger_failure;
      end if;
      if error_code=50017 then
      message('duration must be between 0 and 23');message(' ',no_acknowledge);
      raise form_trigger_fialure;
      elsif
      error_code=40202 then
      clear_message;
      message('please provide the required fields.');message(' ',no_acknowledge);
      raise form_trigger_fialure;
      elsif error_code=50022 then
      clear_message;
      message('time must be in the correct format.');message(' ',no_acknowledge);
      raise form_trigger_fialure;
      end if;
      if error_code in (40509,40508) then
      dbmserrcode:=dbms_error_code; dbmserrtext:=dbms_error_text;
      if dbmserrcode=-1438 then
      clear_message;
      message('your number is too large.try again.');message(' ',no_acknowledge);
      raise form_trigger_fialure;
      elsif dbmserrcode=-1400 then
      clear_message;
      message('your forgot to provide a value.try again.');message(' ',no_acknowledge);
      raise form_trigger_fialure;
      elsif dbmserrcode=-2291 then
      clear_message;
      message('header is not found for this insertion.');message(' ',no_acknowledge);
      raise form_trigger_fialure;
      elsif dbmserrcode=-1 then
      clear_message;
      message('record already exists');message(' ',no_acknowledge);
      raise form_trigger_fialure;
      else
      clear_message;
      message(error_code||' : '||error_text);message(' ',no_acknowledge);
      raise form_trigger_fialure;
      end if;
      else
      clear_message;
      message(error_code||' : '||error_text);message(' ',no_acknowledge);
      raise form_trigger_fialure;
      end if;
      exception
      when others then
      if dbms_error_code=-20738 then
      clear_message;
      message('Error: absent or later record errror from the database trigger.);
      raise form_trigger_failure;
      elsif .....
      ......
      ....
      esle
      clear_message;
      message(error_code||' : '||error_text);message(' ',no_acknowledge);
      raise form_trigger_fialure;
      end if;
      end;
      any suggession?
      Regards
        • 1. Re: Error Handling in Forms
          Andreas Weiden
          You could start with formatting the code so that the structure is recognizable. And you have to be more specific about your "unexpected errors". Which error is shown twice? Which one is shown never?
          • 2. Re: Error Handling in Forms
            InoL
            MESSAGE is really not a very good way to show errors. Messages are placed on a stack and you have to acknowledge a message before the next one is shown. Use alerts instead.
            sometime important message never displayed.
            i just set the :system.message_level =20 in the when-new-form-instance trigger.
            Don't do that. It will skip system error messages.
            • 3. Re: Error Handling in Forms
              Maahjoor
              hi inlol,

              yes, the alerts is okay,i will consider this,

              but the setting of message_level, we need that, since if we did'nt set that, then when the user click save, our save alert will display,
              but the oracle own message, TRANSACTION COMPLETE: ONE RECORD APPLIED AND SAVED. will also be displayed after the user close the alert.

              we really dont have any idea, how to handle the error and information messages in oracle developer.
              we just need our own messages to be displayed.
              any sample trigger? any suggestion?

              Thanks
              • 4. Re: Error Handling in Forms
                InoL
                any sample trigger?
                Google for it! First entry:
                http://www.foxinfotech.in/2013/04/on-error-trigger-with-log-oracle-forms.html
                • 5. Re: Error Handling in Forms
                  CraigB
                  Error handling is the most abused process in just about all programming languages. You should never treat error handling casually. I strongly recommend you take at least several days to map out how you want to handle specific errors and then design system to handle these specific messages as well as handle any unexpected errors.

                  Once you have figured out how you want errors handled - now you can write the code to handle them. I recommend you place this standard error handling procedure in a PL/SQL Library and attach it to all forms. I also recommend you create a default ON-ERROR trigger that you subclass to all of your forms as well. This will ensure that all of the forms in your application handle errors the same way and gives you the flexibility of being able to extend the ON-ERROR trigger in specific forms where the default method needs to be enhanced.

                  I agree with InoL that you should not set the :SYSTEM.MESSAGE_LEVEL = 20 in your When-New-Form-Instance (WNFI) trigger. This will hide errors that you think you are handling in your On-Error trigger because you have instructed the system to supress them. You should set the system message level sparingly and only in specific situations. The standard error handling procedure I mentioned above is an evolutionary process because as your application gets used more and more, you will discover new error scenarios that you want/need to handle. By having a standardized error handling mechinism, it allows you to grow your error handler and push the changes out to all of your forms without having to modify all of your forms. That is the beauty of PL/SQL libraries and subclassed objects (like the On-Error trigger).

                  Now that I've lectured on standardized error processing, I'll comment on your code. ;)

                  The first thing I would do is remove setting the :SYSTEM.MESSAGE_LEVEL = 20 in your WNFI trigger.
                  Second...
                  ...
                    --------- user defined database exceptions errors raised by --RAISE_APPLICATION_ERROR
                    IF DBMS_ERROR_CODE in (-20738,.......) then
                      /* You Fail the form here, but you don't tell the user why! */
                      /* Leaving your user to wonder what happened! */
                      /* You should always display something to the user when you fail the form. */
                      raise form_trigger_failure;
                    end if;
                  ...
                  Without digging too deep through your code sample, the rest of your On-Error trigger looks OK accept for the use of the Message() built-in. I agree with Inol you should use an Alert instead.

                  Craig...
                  • 6. Re: Error Handling in Forms
                    Maahjoor
                    Thanks CariqB,

                    i agree with you, and i am already thinking like you said the libraries and sub-classing,
                    but for that, i must come with a plsql code which handle every error and suppress unnecessary messages.
                    i try my best but failed to produced such a code,
                    thats why i am now thinking to move to APEX.
                    APEX have with its other benefits, the error handling default mechanism.

                    secondly what you mansion
                    --------- user defined database exceptions errors raised by --RAISE_APPLICATION_ERROR
                      IF DBMS_ERROR_CODE in (-20738,.......) then
                        /* You Fail the form here, but you don't tell the user why! */
                        /* Leaving your user to wonder what happened! */
                        /* You should always display something to the user when you fail the form. */
                        raise form_trigger_failure;
                      end if;
                    of course i will do inform the user about the error raised, that code is just a sample,

                    could we implement the oracle ebusiness error handling procedures here in our application?
                    if yes, then where could we find that procedure?

                    Thank you so much for the time and guidance. the comments you guys posting are really helpful.

                    Regards.
                    • 7. Re: Error Handling in Forms
                      InoL
                      thats why i am now thinking to move to APEX.
                      Strange that error handling is your motivation. Forms and Apex are two very different environments. If you are already using Forms, you can't just move to Apex. You will need to rebuild your whole application.
                      If you are starting to build a completely new application, than you should put all aspects into consideration.
                      could we implement the oracle ebusiness error handling procedures here in our application?
                      Probably, if your forms are an extension of EBS. You have to ask this in the EBS forum.
                      • 8. Re: Error Handling in Forms
                        Andreas Weiden
                        APEX have with its other benefits, the error handling default mechanism.
                        Forms also has this default error-handling. Simply remove your ON-ERROR-trigger, then you have a default. That's nothing better or worse than what apex has.

                        For your own error-handling... The "simplest" way in my eyes is create your own error-table where you have appropiate columns for the error-code and the error-type (FRM,ORA,Whatever), then in your ON-ERROR-trigger read the appropiate record from that table and show the error. You could also have a flag in your table which decides what to do with a specific error (Show as Message, Show as Alert, Don't show at all) and react accordingly.

                        TO be honest, i can't see your point of why error-handling being such a complex thing.
                        • 9. Re: Error Handling in Forms
                          CraigB
                          i must come with a plsql code which handle every error and suppress unnecessary messages.
                          Good luck with that. No matter how well thought out or planned, you're not going to be able to take all scenarios into account. The design of an exception handler takes time to address all possible scenarios you want handled and even then, you're not likely to handle every situation in the beginning.
                          could we implement the oracle ebusiness error handling procedures here in our application?
                          if yes, then where could we find that procedure?>
                          I've dug through that code and it is not a thorough as you think it is. It handles specific situations and then defaults the rest. It is a good model for your own exception handler, however. That is what I used it for. :) I don't recall which library it is in, but I'll look around and if I find it, I'll post it.

                          I agree with Andreas's comment...
                          Simply remove your ON-ERROR-trigger, then you have a default. That's nothing better or worse than what apex has.
                          I'm not too familiar with Apex; I've worked with it, but it's been a little while. Even with Apex, you're going to have to develop a customized Exception Handler so more intuitive or application specific messages are displayed in response to an exception.

                          As to Andreas's comment...
                          For your own error-handling... The "simplest" way in my eyes is create your own error-table where you have appropiate columns for the error-code and the error-type (FRM,ORA,Whatever), then in your ON-ERROR-trigger read the appropiate record from that table and show the error. You could also have a flag in your table which decides what to do with a specific error (Show as Message, Show as Alert, Don't show at all) and react accordingly.
                          This is exactly what we implemented in our application. We created a set of tables that contain all of the Oracle Runtime errors and their attributes (Error Type, Severity Level, etc). Then we wrote a standard On-Error trigger that is subclassed to all of our forms that calls a Library "On_Error" package. This package evaluates the exception raised, compares it to the database entry for the error and then displays an appropriate message depending on the Severity Level, Error Type (ORA or FRM) and Message Type (Informational, Error, Caution) and we display the message in either the Status Bar, Note Alert, Caution Alert or Stop Alert or it is supressed. If an error is not found in the table, then it is displayed in a Stop Alert as an "Unhandled Exception." The second table I mentioned contains all of the messages used by our application and is cross referenced with the table that contains the Oracle messages. By simply assigned one of our messages to an Oracle message - our package will display our message instead of the default Oracle Message. Yes, this took sometime to build and test and once implemented, we spent another couple of months perfecting it, but now it is pretty solid and we haven't had to modify it in over a year.

                          While this level of complexity may be overkill for your application, it does offer the greatest flexibility in the long-run. Also, if your application must support multiple languages, having all of your messages in a table make internationalization extremely easy. :)

                          The point of all this, is that no matter which system you use (Forms, Apex, or Java) you will still need to invest some time into designing an Excpeiton Handling method for your application and then implement it accross the application. This is not something you can develop in a short period of time and it will not be perfect in the beginning. However, in the long-run, you will be happy you spent the time up-front to build a flexible and robust Exception Handling system.

                          Craig...