From: bs794@cleveland.Freenet.Edu (Diana Tracy) Newsgroups: comp.databases.oracle Subject: Re: ORACLE 7 DB Trigger Error Messages Date: 30 Jan 1994 04:37:28 GMT Organization: Case Western Reserve University, Cleveland, OH (USA) Lines: 60 Message-ID: <2ifdi8$4ee@usenet.INS.CWRU.Edu> References: Reply-To: bs794@cleveland.Freenet.Edu (Diana Tracy) NNTP-Posting-Host: eeyore.ins.cwru.edu In a previous article, clement1@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@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@cleveland.Freenet.Edu -- and Really Wild Things