Handling ORA errors in APEX

One of the first things I noticed when making an APEX application, there was a lack of error handling. In particular, the error handling raised from an database procedure. For clarity, here a part of a screen as you will see after a unique constraint violation, the ORA-0001 error. I think the most horrible part is that this error is displayed on a new page!

Errors like these must be displayed on the same page where the error occured, on top of the page,  in the #NOTIFICATION_MESSAGE# area, the same way as page processing validation is displayed. Here a preview of the final result.

This of course can be achieved by adding an extra page process validation. Page process validation is to be conducted before the page procesess. Major disadvantage of this solution is that all business rules which are implemented in the database, must be defined as a page process validation on each APEX page. Moreover, if we adding a business rule on the database, we must not forget to add an extra process validation on a ore more APEX pages.

Make sure that all errors are handled by one procedure (a good habit anyway). something like this:

exception
  when others then
    omp_log.error(sqlcode,sqlerrm);
end ins;

In this procedure, we test of this error has occurred in an APEX environment. If so, we DON’T raise this as a application error. Moreover, we remember through a package variable that an error has occurred which had not been raised.

if v('APP_SESSION') is null then
   pv_set_unraised_error_exists := false;
   raise_application_error(sqlcode,sqlerrm);
else
   -- context is APEX
  pv_set_unraised_error_exists := true;
   -- save the
   omp_nos.ins(sqlerrm);
end if;

We also save the error occurred in a temporary table. We must do that with an autonomous transaction so that the errors are actually recorded in the temporary table. We can not keep these errors in a package pl/sql table because the web, and therfore APEX is stateless and everytime we (re-)load an APEX page, we have got an new oracle session.

create global temporary table omp_notifications
( session_id number not null enable
,text varchar2(4000 byte) not null enable
) on commit preserve rows ;

create or replace
package body omp_nos as

procedure ins(p_sqlerrm in varchar2 ) is
   pragma autonomous_transaction;
begin
  insert into omp_notifications
   (session_id  ,text)
  values
  (v('APP_SESSION'),p_sqlerrm );
  commit;
end ins;

end omp_nos;

Now the process itself.
Create an 1:1 view on the table (create view as select * from my_table my_view). Also create instead of triggers on this view. These triggers, in turn, calls procedures to insert, update or delete a row on the accompanying table. These procedures, or course, redirects all errors to the main error procedure. The form on an APEX page must be based on this VIEW with the instead of triggers, not on the table. This is the only way to cach all your DML errors.

After the process fails, eg due to a business rule violation or an unique constraint violation, we have to do an rollback. Indeed, we did not raised an application error, we wrote all the errors in an temparary table. Therefore we create a second page process (with a higher sequence is the main process which handles the DML)

The omp_log.rollback_on_error is quit simple, only if we have unraised errors we perform a rollback.

if pv_set_unraised_error_exists then
  rollback;
end if;

Next we have to make an extra conditional branch, On Submit, After Processing we have to branche to page &APP_PAGE_ID. (the page himself)

The function omp_log.unraised_error_exists returns the value of the package variable pv_set_unraised_error_exists.
There is just one more thing todo. We must show all the error’s we saved in the tempory table. Therefore we call another package procedure, omp_log.show_global_notifications.

That is all. It looks much, but almost everything is implemented in generic database procedures. all you need to do is for each APEX page, add 3 extra call to these database procedures. These calls are always the same, you can cut and past them.

Below is the code for composing and displaying the notification.

procedure show_global_notifications is

  cursor c_nos(b_session_id number) is
     select nos.text
      from omp_notifications nos
    where nos.session_id = b_session_id;

  l_text         varchar2(400);
  l_notification varchar2(4000);

  l_nl           varchar2(20);
   l_session_id   number;
   l_error_count  number := 0;

begin
   l_session_id  := v('APP_SESSION');

  l_nl := '<br/>';
   l_notification := '<ul>';

  for r_nos in c_nos(l_session_id) loop
     l_error_count := l_error_count + 1;
     l_text := '<li>'||replace(r_nos.text,'User-Defined Exception',null) || '</li>';
     l_notification := l_notification || l_text;
  end loop;

  case l_error_count
     when 0 then null;
     when 1 then l_notification := '1 error has occurred' || l_nl ||l_notification;
     else l_notification := to_char(l_error_count)||' errors have occurred' || l_nl ||l_notification;
   end case;

  l_notification := l_notification || '</ul>' ;

  if l_error_count > 0 then
     -- set the notification message
     apex_application.g_notification := l_notification;

    -- suppress the defined Process Success Message
     apex_application.g_print_success_message := null;

    delete omp_notifications nos
      where nos.session_id = l_session_id;
   end if;

end;

Ratings:

VN:F [1.9.13_1145]

please wait...

Rating: 4.5/5 (2 votes cast)

Tags: Apex, error handling

• 

 Print This PDF Version