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?
We do have a error handling procedure, which is similar to the one in APEX help documentation..
Would that have changed it??
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.
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.
Member of the APEX development team
My Blog: http://www.inside-oracle-apex.com
APEX Plug-Ins: http://apex.oracle.com/plugins
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.05750 0.00007 ...Error data: 4 0.05757 0.00006 ......message: AJAX call returned server error ORA-20099: raise application error for Execute PL/SQL Code. 4 0.05763 0.00006 ......is_internal_error: true 4 0.05769 0.00005 ......apex_error_code: APEX.AJAX_SERVER_ERROR 4 0.05774 0.00005 ......ora_sqlcode: -20099 4 0.05779 0.00006 ......ora_sqlerrm: ORA-20099: raise application error 4 0.05785 0.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.