December 05, 2006

ON-ERROR and ON-MESSAGE-trigger

Many developer have problems with messages which popup in forms, for example "FRM-40401: No changes to save".

Then they look for workarounds and one of the easiest is manipulating the :system.message_level:

KEY-COMMIT - trigger on form-level (quick and dirty)


BEGIN
:System.Message_Level := 25;
COMMIT;
:System.Message_Level := 5;
END;


or KEY-COMMIT - trigger

DECLARE
V_Message_Level NUMBER;
BEGIN
V_Message_Level := :System.Message_Level;
:System.Message_Level := 25;
COMMIT;
:System.Message_Level := V_Message_Level;
END;


these are not best practices. Because you have to write those codes in hundreds of procedures and tons of code.

Filtering the errors and messages is the key to have a powerful message-handling. Here is an easy procedure which shows you the technique:

ON-ERROR - trigger on form-level

DECLARE
V_Error_Code NUMBER;
V_Error_Text VARCHAR2 (2000);
V_DBMS_Error_Code NUMBER;
V_DBMS_Error_Text VARCHAR2 (2000);
BEGIN
V_Error_Code := Error_Code;
V_Error_Text := Error_Text;
V_DBMS_Error_Code := DBMS_Error_Code;
V_DBMS_Error_Text := DBMS_Error_Text;

IF V_Error_Code IN (40401, 40405) THEN
/*
|| 40401, 40405 - no changes to save / apply get filtered
*/
NULL;
ELSIF V_Error_Code IN (-1034, -3114) THEN
/*
|| -1034, -3114 - not connected to database
*/
Message ('Not connect to database, exiting Form');
Exit_Form (no_validate);
ELSIF V_Error_Code IN (40508, 40735)
AND V_DBMS_Error_Code BETWEEN -20999 AND -20000 THEN
/*
|| -20000 errors are raised by RAISE_APPLICATION_ERROR
|| They are handled in a different way
*/
Show_and_Log_DB_Error (V_DBMS_Error_Text);
ELSE
/*
|| All other errors went into Show_and_Log_Error, where they
|| get inspected, analyzed and logged.
*/
Show_and_Log_Error (V_Error_Code);
END IF;
END;

5 comments:

Anonymous said...

Hello sir,

I tried the same code in on-error trigger in forms but showing me compilation error
the identifier'Show_and_Log_DB_Error 'and Show_and_Log_Error'
must be declared

Gerd Volberg said...

that's correct. Those routines point to your logging-procedures. If you want to create such a technique read here: http://talk2gerd.blogspot.com/2007/09/easy-logging-and-debugging-in-forms.html

Anonymous said...

Hi,

Suppressing error messages in the on-error trigger needs to be done judiciously. Ever since a frustrating experience with a difficult bug, I start out suppressing nothing in the on-error trigger. The bug in question was something simple that would have been revealed immediately had the message not been suppressed. It is only after I am sure the form works properly in every situation (with data, without data, during edits, during validation, during navigation, during save, after save) that I dare to begin suppressing error messages -- in other words, it needs to be almost the last thing done to the form.

_daniel_ said...

Nice..

Ali Hassan said...

Thanks Dear!

I tried ON-MESSAGE trigger and solved the problem.

I use to avoid FRM-40503 error message and used the above code and solved problem.

once again thanks.

Ali