APEX 19.2 quick tip: error messages for PL/SQL Dynamic Content regions

Sometimes I use a region of type PL/SQL Dynamic Content. It is rare and there are often better alternatives – like using a select over a pipelined table function – but it still happens.

If the plsql code errors out, then the error is not shown on the same page, but instead is presented as a page rendering error.

Here is a simplified example:

The PL/SQL Dynamic Content region looks like this

   sys.htp.p('Hello APEX world!');
   raise_application_error(-20201, 'Virus detected, world stopped!');

Running the page results in an ugly rendering error on a separate error page. See right display.

Remember the “Technical Info” part is not shown to the end user. So the error message is not shown.

Here is what I did to improve the error presentation. There might be better ways to do it, but it was a quick win and worked for me. So it might help you as well.

1. Capture the error message and write it to the page.
Here I used a hidden div. The class “sqlerror” is used to mark the div.

begin
  sys.htp.p('Hello APEX world!');
  raise_application_error(-20201, 'Virus detected, world stopped!');
exception
  when others then
    -- currently this does not show the error at the page. This would work in a page process.
    apex_error.add_error (
      p_message          => sqlerrm,
      p_display_location => apex_error.c_inline_in_notification 
    );     
    
    --write error into hidden page item
    sys.htp.p('<div class="sqlerror" style="display:none">'||sqlerrm||'</div>');
end;  

2. After page load, use a dynamic action to find this error and display it.

We can not use the “After Refresh” event since PLSQL Dynamic Content regions are not refreshable. So it must be “Page Load”.

// test if there was an error during rendering of any region on the page
if ($('.sqlerror').length) {

    //First clear the errors
    apex.message.clearErrors();

    // Now show new error
    apex.message.showErrors([
        {
            type:       "error",
            location:   [ "page" ],
            message:    $('.sqlerror').text(),
            unsafe:     false
        }]);    
}

Result:

A major difference is that everything that was already rendered at the time of error is now shown in the region. This might be wanted or could be unwanted.

Also we essentially hide the error from the apex engine. So when inspecting /utilities/Debug Messages/ we don’t see that error anymore.

Before the change we would get a debug entry like this

Since we suppressed all errors, we need to call apex_debug.error inside the plsql exception handler.

    
    -- APEX debug  instrumentation call
    apex_debug.error ('Rendering of region failed! %s', sqlerrm);

And then we get such an entry in the APEX debug stack.

tested in APEX 19.2 and 20.1. It should also work in older versions, although the javascript api apex.message.showErrors might need to be replaced with some custom logic.

conclusion

The region type “PL/SQL Dynamic Content” is very different to normal APEX regions. Do not forget to implement and test some basic error handling if you must use such a region type.

Side node: In theory you should also watch out for an XSS attack. If somebody manages to add malicious code into the error message, it might be shown on the page. I didn’t test for that however.