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.