Exception Handling

From: John King <jbking_at_erols.com>
Date: 1997/06/18
Message-ID: <01bc7c15$1bd48040$182fa8c0@NCCI.ncci.cuc.com>#1/1

RDBMS Version (if applicable): 7.3.2.3.1 Error Number (if applicable): N/A
Product and Version: Designer/2000 1.3.2 (32-bit) Operating System: MS Windows 95

I'm trying to create a method of handling user-defined error messages that are raised from within functions and procedures that are called by database triggers. I'd like the method to be robost enough to cover both the backend entry to the database (sql) and the frontend (forms), yet flexible enough to allow the messages to be modified and updated easily.

I created a db stored package called ErrorHandler using the module logic navigator which has the
user-defined exceptions and a corresponding constant declared in the package specification. For example:

CREATE OR REPLACE PACKAGE ErrorHandler IS
--

e_RestrictedInsert EXCEPTION;
--

v_RestrictedInsert CONSTANT NUMBER DEFAULT -20001;
--

e_InvalidDigit EXCEPTION;
--

v_InvalidDigit CONSTANT NUMBER DEFAULT -20002;

. 
. 
. 

END ErrorHandler;

There are also procedures and functions for querying a table for the appropriate error message and error type based on the error code (i.e., -20001).

My problems seems to be that, if the person is accessing the table from sql, the error needs to be displayed using a RAISE_FORM_TRIGGER. But, if the user is accesssing the database through a forms application I need to display an alert and RAISE_FORM_TRIGGER_FAILURE, which I use the MSG_ALERT procedure included with the standard template.

Also, if I try to access a publicly declared variable or exception from Forms (i.e., ErrorHandler.e_InvalidDigit), then I get a compilation error on the variable, but not the package. However, I can access these variable with no problems in the database trigger code or a stored procedure.

Do you have a suggestion on how to make this all work together? Received on Wed Jun 18 1997 - 00:00:00 CDT