4 Replies Latest reply: Jul 19, 2013 6:04 AM by Tom Petrus RSS

    Raise Application Error on Dynamic Action

    JSonySCC

      Hi

      I am using Apex 4.2. On a click of button a Dynamic Action is written, which calls a DB Package. The DB package has exception handlers and also the package has two output parameters

      Error Code and Error Msg

      So in my code(Dynamic Code) i make a call to the package and checks the value for Erroc Code.

      Begin

      Package call......
        If v_error_code <> '0' Then
          Raise SC_Error;
        End if;

      Exception

        When SC_Error Then
          RAise_Application_error(v_err_code, v_err_msg);

      End;

      But when i run the page and I click I get an Internal exception error

      Output from Debug -- I am not sure what the Cell in Red colour means, because the package is returning a correct message (before the red line).Can anyone help please??

      Thanks

      JS

      0

      ......message: AJAX call returned server error ORA-20003: Customer has already been matched for Execute PL/SQL Code.4

      0

      ......is_internal_error: true4

      0

      ......apex_error_code: APEX.AJAX_SERVER_ERROR4

      0

      ......ora_sqlcode: -200034

      0

      ......ora_sqlerrm: ORA-20003: Customer has already been matched4

      0

      ......error_backtrace: ORA-06512: at line 41 ORA-06512: at "SYS.DBMS_SYS_SQL", line 1926 ORA-06512: at "SYS.WWV_DBMS_SQL", line 973 ORA-06512: at "SYS.WWV_DBMS_SQL", line 999 ORA-06512: at "APEX_040200.WWV_FLOW_DYNAMIC_EXEC", line 830 ORA-06512: at "APEX_040200.WWV_FLOW_PLUGIN_UTIL", line 2304 ORA-06512: at "APEX_040200.WWV_FLOW_DYNAMIC_ACTION_NATIVE", line 442 4

      0

      ......component.type: APEX_APPLICATION_PAGE_DA_ACTS4

      0

      ......component.id: 231852037977562094

      0

      ......component.name: NATIVE_EXECUTE_PLSQL_CODE4

      0

      ...Execute Error Handling Callout defined on Page or Application Level4

      0

      ...Execute Statement: begin declare l_error wwv_flow_error_api.t_error; begin l_error := wwv_flow_error_api.g_error; wwv_flow_error_api.g_error_result := apx_error_utils.handleerror ( p_error => l_error ); end; end;4

      0

      ......Values changed by callout:4

      0

      .........message: An unexpected internal application error has occurred. Please get in contact with your system administrator and provide reference# for further investigation.4

      0

      .........display_location: ON_ERROR_PAGE4

      100

      Stop APEX Engine detected4

      0

      Final commit4-
      1 - 59
        • 1. Re: Raise Application Error on Dynamic Action
          Tom Petrus

          When I write a dynamic action with a plsql block in it and put a raise_application_error in it, I get an alert displaying the error (my raised error number and message that is).

          Do you have an error handling procedure set up for your application or page, since that seems to change the values?

          • 2. Re: Raise Application Error on Dynamic Action
            JSonySCC

            Hi Tom

            We do have a error handling procedure, which is similar to the one in APEX help documentation..

            Would that have changed it??

             

            Thanks


            • 3. Re: Raise Application Error on Dynamic Action
              Patrick Wolf-Oracle

              Hi,

               

              yes it does. It's the following part of the error handling function which changes any APEX engine error to a more generic error.

               

              if p_error.is_internal_error then

                -- Access Denied errors raised by application or page authorization should

                -- still show up with the original error message

                if p_error.apex_error_code <> 'APEX.AUTHORIZATION.ACCESS_DENIED' and p_error.apex_error_code not like 'APEX.SESSION_STATE.%' then

               

              Add APEX.AJAX_SERVER_ERROR to the exceptions where you don't want to have any modifications.

               

              For example:

               

                  if p_error.is_internal_error then
                       -- Access Denied errors raised by application or page authorization should
                       -- still show up with the original error message
                       if    p_error.apex_error_code not in ( 'APEX.AJAX_SERVER_ERROR', 'APEX.AUTHORIZATION.ACCESS_DENIED' ) and p_error.apex_error_code not like 'APEX.SESSION_STATE.%' then
              

               

              You can further improve that code with

               

              if p_error.is_internal_error then
                   if p_error.apex_error_code = 'APEX.AJAX_SERVER_ERROR' and p_error.ora_sqlcode between -20999 and -20000 then
                       l_result.message := apex_error.get_first_ora_error_text (
                                               p_error => p_error );
                   elsif p_error.apex_error_code <> 'APEX.AUTHORIZATION.ACCESS_DENIED' and p_error.apex_error_code not like 'APEX.SESSION_STATE.%' then
               

               

              Which will change the error message to just return "Customer has already been matched for Execute PL/SQL Code." in the case a custom error message was raised with raise_application_error.

               

               

              Regards

              Patrick


              Member of the APEX development team

              My Blog: http://www.inside-oracle-apex.com

              APEX Plug-Ins: http://apex.oracle.com/plugins

              Twitter: http://www.twitter.com/patrickwolf

              • 4. Re: Raise Application Error on Dynamic Action
                Tom Petrus

                Edit: Well, Patrick beat me to the punch because I was taking my jolly time writing this down

                 

                Well, if you look below the part you put in red, you can see that a callout is made: ...Execute Error Handling Callout defined on Page or Application Level

                With directly below that:

                ......Values changed by callout:4

                 

                .........message: An unexpected internal application error has occurred. Please get in contact with your system administrator and provide reference# for further investigation.

                Since the plsql call is on the serverside, this error is trapped within the error handling function aswell. Your error has simply been obscured by it, and probably because it looks at the error backtrace and determines the error is internal.

                 

                Looking at the sample error handling code, this is probably where it goes "wrong":

                -- If it's an internal error raised by APEX, like an invalid statement or
                  -- code which cannot be executed, the error text might contain security sensitive
                  -- information. To avoid this security problem rewrite the error to
                  -- a generic error message and log the original error message for further
                  -- investigation by the help desk.
                  if p_error.is_internal_error then
                
                

                 

                I recreated the situation here, and put some debug in the error handling function just so I could see what sort of error is being processed:

                    apex_debug.message('error code: '||p_error.apex_error_code);
                    apex_debug.message('sql code: '||p_error.ora_sqlcode);
                    apex_debug.message('sql errm: '||p_error.ora_sqlerrm);
                    apex_debug.message('error_backtrace: '||p_error.error_backtrace);
                
                

                 

                0.057500.00007...Error data:4

                0

                0.057570.00006......message: AJAX call returned server error ORA-20099: raise application error for Execute PL/SQL Code.4

                0

                0.057630.00006......is_internal_error: true4

                0

                0.057690.00005......apex_error_code: APEX.AJAX_SERVER_ERROR4

                0

                0.057740.00005......ora_sqlcode: -200994

                0

                0.057790.00006......ora_sqlerrm: ORA-20099: raise application error4

                0

                0.057850.00005......error_backtrace: ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_SYS_SQL", line 1926 ORA-06512: at "SYS.WWV_DBMS_SQL", line 1064 ORA-06512: at "SYS.WWV_DBMS_SQL", line 1090 ORA-06512: at "APEX_040200.WWV_FLOW_DYNAMIC_EXEC", line 832 ORA-06512: at "APEX_040200.WWV_FLOW_PLUGIN_UTIL", line 2304 ORA-06512: at "APEX_040200.WWV_FLOW_DYNAMIC_ACTION_NATIVE", line 442

                 

                As you can see, the ajax error is an internal error, and that is why it is handled as such in the error handler. With this info you should be able to adjust your handling function. Test for the error code (APEX.AJAX_SERVER_ERROR) and then throw the error message or test for the -20000 range of custom errors for example.

                 

                    if p_error.is_internal_error then     
                        -- Access Denied errors raised by application or page authorization should
                        -- still show up with the original error message
                        -- do not parse ajax server errors here !
                        if    p_error.apex_error_code <> 'APEX.AUTHORIZATION.ACCESS_DENIED'
                          and p_error.apex_error_code not like 'APEX.SESSION_STATE.%'
                          and p_error.apex_error_code != 'APEX.AJAX_SERVER_ERROR'
                        then
                
                

                It probably does need some more work but you should get the gist of it.