Re: Getting error messages back to FORM from database triggers

From: Grue <grue_at_hotmail.com>
Date: 1998/01/28
Message-ID: <34ccb3b3.7382833@128.158.254.10>#1/1

Here's how we do it. In your ON-ERROR trigger in your form, do the following:

---

a1		NUMBER;
alt_message	VARCHAR2(200); 
alt_num		NUMBER;
alt_id		ALERT;

BEGIN

IF MESSAGE_CODE IN ('40504', '40506', '40507',
                       '40508', '40509', '40510', '40511', '40512',
                       '40513') THEN

    /* Check for database trigger failure */     IF INSTR(DBMS_ERROR_TEXT, 'ORA-06512', 1) != 0 THEN

        /* Find end of trigger message */
        a1 := INSTR(DBMS_ERROR_TEXT, 'ORA-06512', 1) - 1;

        /* Display trigger message */
        alt_message := SUBSTR(DBMS_ERROR_TEXT, 12, a1-12);

        SET_ALERT_PROPERTY(alt_id, ALERT_MESSAGE_TEXT, alt_message);

        IF alt_message IS NOT NULL THEN
            alt_num := SHOW_ALERT(alt_id); 
            RAISE FORM_TRIGGER_FAILURE;
        END IF;

        RAISE FORM_TRIGGER_FAILURE;

    END IF;
END IF; END;

---

Hope that helps.

On 23 Jan 1998 17:23:30 GMT, "xinsun" <xin.sun_at_memo.ikea.com> wrote:

>When it comes to error handling the FORM has a definitely advantages by
>using the FORM_TRIGGER_FAILURE exception. But none one of this
>functionality is directly called by any database trigger. I run into a
>problem when the trigger is failed, my user_defined exception error
>handling messages is not come back to forms.(neither FORM 3.0 nor FORM4.5)
>although FORM did aware of there is a failure in the database, so it is
>only simply display 'Unable to update/delete/insert record' which is not
>very helpful to me and the user.
>
>I was able to get the error message from stored procedure back to FORMS but
>when I try the same trick it does not seem to work.
>
>Has anyone had any experience in getting error message back to FORM from
>database trigger? Please Help!
>
>Thanks in advance.
>
>Xin Sun
>
>Email address: xin.sun_at_memo.ikea.com
>
Received on Wed Jan 28 1998 - 00:00:00 CST