Re: ORACLE 7 DB Trigger Error Messages

In a previous article, clement1_at_llnl.gov (David Clement) says:

>Something a little flaky has come up in error messages from my ORACLE 7
>database triggers. I can't find the explanation in the documentation or
>the FAQ and I hope someone in the newsgroup can advise me.
>
>The environment is SQL*Plus 3.1.2.3.1, PL/SQL 2.0.17.1.0, ORACLE 7.0.15
>with procedural and distributed options, under SunOS 4.1.2 on a Sun4
>server. The triggers (there are about 25 of them) perform various input
>and ref-int validations. For example:
>
>CREATE TRIGGER BLAH
>BEFORE INSERT ON WOOF
>FOR EACH ROW
>BEGIN
> IF :NEW.ONE_CODE = 'Y' AND :NEW.OTHER_CD != '99' THEN
> RAISE_APPLICATION_ERROR(-20000,
> 'Cannot save invalid code combination');
> END IF;
>END;
>
>I can insert with a valid code combination. When I try to insert with
>an invalid code combination, SQL*Plus responds with --
>
>ORA-20000: Cannot save invalid code combination
>ORA-06512: at line 3
>ORA-04088: error during execution of trigger 'BLAH'
>
>This behavior is consistent from trigger to trigger, whether the code
>uses RAISE_APPLICATION_ERROR nakedly (as above) or has a PRAGMA
>EXCEPTION_INIT to wrap it. The first message is what I wanted; the
>other two are confusing. There is nothing visibly wrong with the code,
>and it behaves as expected. So why am I getting the other two messages?
>Can I turn them off? Should I?
>
>Thanks in advance for your kind attention to my silly question. If
>your answer is, 'It just does that! Bwah-hah-hah!', I shall understand
>perfectly.
>______________________________________________________________________
>David Clement || Geyr Garmr mjok fyr Gnipahellu.
>clement1_at_llnl.gov || Festr mun slitna, enn freki renna.
>______________________________________________________________________
>

It just does that! Bwah-hah-hah!

Actually, it is PL/SQL 2s nice little way to help you debug your program. I personally love those messages: they give the precise line number of your code that raised the exception. In a situation where you have many procedures calling other functions and procedures, it stacks ALL the line where the exception happened.

DON'T turn it off.

By the way, I found the explanation in the User Message and Codes manual under ORA-06512.

-- 
Diana Tracy, System Designer		-- Excitement, Adventure
bs794_at_cleveland.Freenet.Edu		-- and Really Wild Things