03 January 2012

APEX: Friendlier exception message from AJAX call

While it is very easy to create a "Form and a Report" on a single table (or view), just follow the wizard, for end users it is not always intuitive that they should navigate to the form page to remove the record. A nicer solution is the one described by Anthony Rayner in this demo page. This solution uses a number of -very simple- dynamic actions to include a little trashcan on the report page.
In this blogpost I will not describe how to create the trashcan functionality in the report, this is already done by Anthony, but about how to handle this:
You might not be scared by this message, but your users might be. Probably most of them have no idea what is meant by
AJAX call returned server error ORA-02292: integrity constraint (NUIJTEN.SYS_C006303602) violated - child record found for Execute PL/SQL Code.
This exception occurs when you create the PL/SQL Code to delete the department like this
Instead of handling the exception in the application, I prefer to have actions done in stored procedures - preferably packages. So to remove a department I will write a procedure like this:
Here is the code so you don't have to type it in, but can copy and paste it.
create or replace procedure remove_dept (p_deptno IN NUMBER)
is
   e_constraint exception;
   pragma exception_init (e_constraint, -2292);
begin
   delete from dept d
    where d.deptno = p_deptno;
exception
   when e_constraint
   then
      htp.p ('{"error":"Department has Employees"}');
end remove_dept;​​
The key thing in the code is the use of HTP in the exception handler. This will return a JSON object with a user friendly error message. Just replace the original delete statement with a call to the procedure:
and when the user tries to remove a department which still has employees, he will see a friendlier message
You can try out an example on my demo pages.

19 comments:

  1. Great example.. Thank you very much.

    ReplyDelete
  2. Hi, could you tell me why do you use htp.p ('{"error": ?
    What does it do?

    ReplyDelete
    Replies
    1. Instead of returning the AJAX- exception (the server error), the original exception is handled and propagated as a JSON message. The browser knows how to handle this and will show an alert with the message.

      Delete
  3. Alex, can you point me to some documentation on how browsers interpret these JSON strings? I can't find anything, but maybe I'm not using the right search terms. Are there other possibilities beyond just {"error":""}?

    ReplyDelete
    Replies
    1. I'm not even sure that there is documentation how browsers interpret these JSON strings. By examining the response which caused the original error message, I modified the error which is being returned from the stored procedure.

      Delete
  4. Nice! Just thought I'd add that it doesn't appear to work if you have Page Items to Return.

    ReplyDelete
  5. Hi Alex, I am also facing the same problem. i have Page Items to Return in Dynamic Action. What i need to do in this situation

    ReplyDelete
    Replies
    1. Are you using a stored procedure to retrieve the values from the database and place them in Page Items? Then you can use exception handling as described.

      Delete
  6. Hi, is this implementation expected to function in Apex 4.1? I'm currently receiving the following error message in IE 8:

    apex_4_1.min.js
    Message: Syntax error
    Line: 16
    Char: 11695
    Code: 0

    ReplyDelete
    Replies
    1. Yes, I believe it should wok in APEX 4.1 - however I don't have the ability to test it, so can't verify. You could try with the developers tools from IE8 to debug it. Does it work in other browsers, like Chrome or Firefox, or does it show an error as well?

      Delete
    2. Hi, thanks for your reply.

      I've now tested in Firefox and experience the same problem. Without specifying a call to htp.p in the exception hanlding block I can see from Firebug that the default JSON return string is of the format {"error":"AJAX call returned server error Execute PL\/SQL Code."}. Which presents the deafult alert to the user, as expected.

      However, when I call the htp.p function in the exception handling block of my code e.g. htp.p('{"error":"test"}'); I see the following exception thrown in Firebug: SyntaxError: JSON.parse: unexpected non-whitespace character after JSON data ...sFunction(h.promise)?h.promise().then(d.resolve,d.reject):d[g](h)}):b[a](d[g])})... apex_4_1.min.js (line 16)

      Therefore I presume the return from htp.p is not being handled correctly by the enclosing call block. Unfortunately I'm not sure how you can get around this issue as the
      apex_4_1.min.js is minimised, and also I'm not confortable that modifying the out of the box Apex JS is a workable solution.

      I have found that raising an application error in the exception handling block gives some control over the error message output, e.g. RAISE_APPLICATION_ERROR(-20001, 'My custom error text'); results in output in the alert dialog of the format "AJAX call returned server error ORA-20001: My custom error text for Execute PL/SQL Code.", but this is of course not as desirable as your original solution.

      I'd be grateful if you have any other ideas on what the issue might be, or how to work around it.

      Many thanks,

      JR

      Delete
    3. Hi JR,
      Do you happen to have a RAISE after the htp.p call?
      If I place a RAISE; after the htp.p ('{"error":"Department has Employees"}'); I get a similar message a you describe.
      The response that is returned (with the RAISE included) is as follows - you can see this in firebug:
      {"error":"Department has Employees"}
      {"error":"AJAX call returned server error ORA-02292: integrity constraint (NUIJTEN.SYS_C006303602) violated - child record found for Execute PL\u002FSQL Code."}

      Removing the RAISE in the exception handler did the trick (in my case)

      Delete
  7. Hi Alex,

    I don't have a RAISE in the exception handler unfortunately, so that isn't my problem.

    I fear I may have confused matters when I mentiuoned the call I was making to RAISE_APPLICATION_ERROR. The bottom line is that if the only statement I have in the Exception handling block is:

    htp.p ('{"error":"Department has Employees"}');

    The exception: SyntaxError: JSON.parse: unexpected non-whitespace character after JSON data ...sFunction(h.promise)?h.promise().then(d.resolve,d.reject):d[g](h)}):b[a](d[g])})... apex_4_1.min.js (line 16) is thrown in the browser and the expected Alert is not displayed.

    I very much appreciate your responses. I can only assume the issue is related to the code in apex_4_1.min.js, which must be different to the version you have tested with both currently an previously.

    JR.

    ReplyDelete
  8. Hi Alex,

    I've done some more investigation on the issue I was experiencing, and I thought I'd post a quick update with my findings. In summary if you want to output a custom error message during an Ajax call, as described in your original post, you can't set the "Page Items to Return" attribute in the Dynamic Action. If you do, an "exception: SyntaxError: JSON.parse: unexpected non-whitespace character after JSON data" is provoked in out of the box Apex *.js file, as I've identified previously.

    Cheers,

    JR.

    ReplyDelete
  9. If you want to use a custom error handler instead of the ugly alert
    popup error message, you can do so by defining your own javascript
    function called "onerror". See example below

    Tested with APEX 4.2.5 and APEX 5 EA2

    Filip van Vooren



    /******************************************************************
    * Function : Custom APEX Dynamic Action (AJAX) error handler
    * Parameter : p_message = Error message to display
    *
    * Remarks
    * If a Dynamic Action of type (PL/SQL) causes an exception, than
    * the APEX ajax call error handler will check if the "window.onerror"
    * function exists for the current window.
    *
    * If that is the case it will be called accordingly, otherwise
    * an alert() popup will be generated with the corresponding error.
    *
    * On PL/SQL side you can influence the error message by writing JSON
    * to the HTTP buffer: htp.p('{ "error" : "my error text" }';
    ******************************************************************/
    function onerror(p_message) {
    // My fancy error handling goes here
    }

    ReplyDelete
  10. When i use htp.p('{"error":"My Error"}') in my PL/SQL Block called by a dynamic action it doesnt work anymore.

    Reason for this is an extra status line in the response. My complete response looks like

    {"error":"My Error"}
    Status: 204 No Content

    Seems like a bug, isn't ?

    I'm using APEX 5.0.1.00.06 on Oracle 12c

    ReplyDelete
    Replies
    1. You have the same effect on your demo_app. Try to delete a department and have a look into firebug.

      Delete
  11. when used it, I faced error (Error: parsererror - SyntaxError: Unexpected token { in JSON at position 37);
    my code is,
    DECLARE
    V_SI_TYPE NUMBER;

    BEGIN

    SELECT 1 INTO V_SI_TYPE FROM DUAL WHERE EXISTS(SELECT ID FROM SALE_INVOICE_HDR WHERE ID = TO_NUMBER(:P24_SI_ID));

    IF V_SI_TYPE IS NULL
    THEN
    SELECT 2 INTO V_SI_TYPE FROM DUAL WHERE EXISTS(SELECT ID FROM DELIVERY_SALE_INVOICE_HDR WHERE ID = TO_NUMBER(:P24_SI_ID));
    END IF;

    IF V_SI_TYPE IS NULL
    THEN
    SELECT 3 INTO V_SI_TYPE FROM DUAL WHERE EXISTS(SELECT ID FROM TAKEAWAY_INVOICE_HDR WHERE ID = TO_NUMBER(:P24_SI_ID));
    END IF;

    RETURN V_SI_TYPE;
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    htp.p ('{"error":"Department has Employees"}');
    RETURN '';

    END;

    ReplyDelete