7 Replies Latest reply on Mar 19, 2018 3:02 PM by Mint-Innit

    Error Catching plsql

    Hamp

      Hello friends,

       

      I have many plsql in my application that I need to catch exception and I was wondering what is the best way to catch error. I have written the following exceptions but somehow I see that I don't receive any notification to show my error.

       

      EXCEPTION
         WHEN NO_DATA_FOUND THEN  
         APEX_ERROR.ADD_ERROR (    
          p_additional_info  => 'USER NAME NOT FOUND' , 
          p_message  => 'PLEASE HELPDESK FOR ASSISTANCE',
          p_display_location => apex_error.c_inline_with_field_and_notif ,
          p_page_item_name => 'P7_Dynamic_action_1');
         WHEN PROGRAM_ERROR THEN
          APEX_ERROR.ADD_ERROR (
          P_MESSAGE => 'PLEASE HELPDESK FOR ASSISTANCE',
          p_display_location => apex_error.c_inline_with_field_and_notif 
          );
          RAISE_APPLICATION_ERROR ( -20001, 'ERROR FOUND IN COMPUTATIONS  UKNOWN ERROR '); 
          
         WHEN TOO_MANY_ROWS THEN
       RAISE_APPLICATION_ERROR ( -20001, 'ERROR FOUND IN COMPUTATIONS TOO_MANY_ROWS ');
      

       

      I used two different type of error but I don't receive any notification for either one of them.

       

      thanks,

      Hamp

        • 1. Re: Error Catching plsql
          Pierre Yotti

          It is difficult to help you without see the complet logic for the code. however when you don't receive any notification, that means the errors are not handle or there is no errors.

           

          Can you give more details how your code look?

           

          Have a look here too

          Nine Good to Knows for PL/SQL Error Management 

          • 2. Re: Error Catching plsql
            Hamp

            Hi Alli, my question is more focused on raising the error and what to capture how to bring it to the attention of the user and it is not code specific.

            • 3. Re: Error Catching plsql
              Pierre Yotti

              Okay can you show your Code? When the error is not catch, that means

               

              maybe you don't catch it.

               

              Can you show your code?

              • 4. Re: Error Catching plsql
                jariola

                APEX_ERROR

                This procedure must be called before the Application Express application has performed the last validation or process. Otherwise, the error is ignored if it does not have a display location of apex_error.c_on_error_page.

                • 5. Re: Error Catching plsql
                  TexasApexDeveloper

                  I would HIGHLY recommend you look at  getting an exception handler added to your application.. Look at this older blog for an example: https://apexplained.wordpress.com/2012/05/18/beginning-error-handling-in-apex-4-1/

                   

                  Thank you,

                   

                  Tony Miller

                  Los Alamos, NM

                  • 6. Re: Error Catching plsql
                    Hamp

                    Hello friends,

                     

                    Sorry I think I missed the last three messages. Well I am catching the errors because the usual Ajax call failed doesn't appear but I can't bring it to the attention of the user or administrator.

                    @Texasapexdeveloper : I have seen custom error catching function but I tried to hesitate doing it like that if there is an alternative built in solution.

                    thanks

                     

                    sina

                    • 7. Re: Error Catching plsql
                      Mint-Innit

                      Did you take note of jariola's comment above? What happens when you change your error display location to apex_error.c_on_error_page ?

                       

                      I have also found that if your PL/SQL is being called only from APEX, there's no need to call RAISE_APPLICATION_ERROR as the message added to apex_error will appear to the user (with the caveat from the comment above!)

                       

                      The technique I used to alert an application administrator to APEX errors is to have a scheduled job that runs hourly throughout the day. It looks in APEX_WORKSPACE_ACTIVITY_LOG for any rows where ERROR_MESSAGE is not null, and that have been created since the last time the job ran. It then looks up who the application administrator is for that application, and emails them the error message. Its a simple method but it works well enough for my needs so far.

                       

                      For handling PL/SQL exceptions I've written my own package which logs exceptions along with technical details to a file (could easily be changed to use a table). It can also be configured to email appropriate recipients as soon as errors occur.  I wrote it before I was aware of the open source project logger: https://github.com/OraOpenSource/Logger which might be worth you looking at for inspiration too.

                       

                      Cheers.