Re: Forms error_text exception

pwu_at_qantas.com.au wrote:
> I'm using Oracle Forms 6i.
>
> I want to write a message to a table log whenever there is an error in
> any trigger's PL/SQL code.
>
> I have a forms trigger WHEN-NEW-FORM-INSTANCE which has PL/SQL code:
>
> declare
> .....
> begin
> exception
> when others then
> insert into log values (ERROR_TEXT||' -
> WHEN-NEW-FORM-INSTANCE);
> end;
>
> The code in the WHEN-NEW-FORM-INSTANCE trigger generates an error.
>
> I get a blank error-text on table log despite the fact that the Forms
> reference manual says that the function ERROR_TEXT can be called at
> any time.
>
> On the second try I code another forms trigger ON-ERROR and remove the
> exception handler in WHEN-NEW-FORM-INSTANCE:
> insert into log values (ERROR_TEXT);
>
> This time I get a non-blank error message.
>
> However I want the exception handler in WHEN-NEW-FORM-INSTANCE and a
> non-blank error message so this time I change the trigger
> WHEN-NEW-FORM-INSTANCE to:
>
> declare
> .....
> begin
> exception
> when others then
> :exception_block.whereami:=' - WHEN-NEW-FORM';
> raise Form_Trigger_Failure;
> end;
>
> I also change trigger ON-ERROR to:
> insert into log values (ERROR_TEXT||:exception_block.whereami );
>
> But this time the ON-ERROR trigger is not run.
>
> How can I have an the exception handler in WHEN-NEW-FORM-INSTANCE and
> get the ON-ERROR trigger to execute?
>
> Thanks for any help.

I agree with Mark that you need to capture the SQLERRM into a variable. But I personally prefer my exception handling at form level in a structure such as this:

DECLARE

Err_Code   NUMBER := error_code;
Err_Type   VARCHAR2(3) := error_type;
Err_Text   VARCHAR2(80) := error_text;

BEGIN
    message('ERROR: ' || Err_Type || '-' || TO_CHAR(Err_Code) ||': '|| Err_Text);

    message(' ');

• If NOT an Oracle database error, then display the form error.
• Otherwise, display the Oracle database error message.

IF error_code NOT IN (

    40501, 40502, 40504, 40505, 40506, 40507, 40508, 40509, 40510,     40511, 40512, 40513) THEN     message('IF: ' || Err_Type || '-' || TO_CHAR(Err_Code) ||': '||    Err_Text);
    message(' ');
   Err_Text);
ELSE
    message('ELSE: ' || dbms_error_text);     message(' ');
END IF; RAISE FORM_TRIGGER_FAILURE; END; And obviously I whacked this a lot give just the outline.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)