Re: Help : Trigger error, Please

From: Henk de Wilde <dewildeh_at_xs4all.nl>
Date: 1998/05/01
Message-ID: <354a1ddb.4915694_at_news.xs4all.nl>#1/1


On Thu, 30 Apr 1998 16:22:04 GMT, Laurent <L.Phelep_at_ex.ac.uk> wrote:

>Hi,
>
>I received these errors messages when I run the following trigger :
>
>create trigger contract_1
>before insert on contract
>for each row
>declare
> two_contract_error exception;
> test number;
>begin
> select count(contract_no)
> into test
> from contract
> where (cust_no = :new.cust_no and
> Real_date_in is NULL);
> if (test <> 0) then
> raise two_contract_error;
> end if;
> exception
> when two_contract_error then
> raise_application_error(-20671, 'This client already has a car
> on loan, rental refused');
>end;
>/
>
>I do receive the following errors :
>
> SQL> _at_ test2;
>insert into contract values(
> *
>ERROR at line 1:
>ORA-20671: This client already has a car
>on loan, rental refused
>ORA-06512: at "OPS$CS95LP.CONTRACT_1", line 15
>ORA-04088: error during execution of trigger 'OPS$CS95LP.CONTRACT_1'
>
>SQL>
>
>I do understand the first one but not
>the number 06512 and 04088
>
>In the book i only have this help :
>
>--ORA-06512 at str line num
>
> Cause:
> This is usually the last of a message stack and indicates where a
>problem occurred in
> the PL/SQL code.
>
> Action:
> Fix the problem causing the exception or write an exception
>handler for this condition.
> It may be necessary to contact the application or database
>administrator.
>

This isn't anything to worry about as long as you're only using Sql*Plus as you seem to be doing. The point here is that you raised a self defined exception in the outer block of your PL/SQL statement.

I assume you are developing this code piece to integrate it in another block. The correct way to go about this is to define your exception in the block where the final exception handling is to take place, then raise this exception anywhere in the _statement_ part of your PL/SQL.

The purpose of exceptions is to propagate errors to the level where they should be handled. This means that when you raise an exception in an exception handler it propagates outside the block. Your error -20671 being unknown in Sql*Plus leads to the other two errormessages.

Assuming that the code in which this is to be integrated is properly setup to handle this error, this will go away as soon as you test this peace of code in its proper setting.

I hope this helps

Henk de Wilde. Received on Fri May 01 1998 - 00:00:00 CEST

Original text of this message