PL/SQL Oracle Error Handling

Question

I've created a trigger that only allows a user to have 10 current placed orders. So now when the customer tries to placed order number 11 the oracle database throws back a error. Well 3 errors.

ORA-20000: You currently have 10 or more orders processing.

ORA-06512: at "C3283535.TRG_ORDER_LIMIT", line 12

ORA-04088: error during execution of trigger 'C3283535.TRG_ORDER_LIMIT'

The top error is one I've created using:

raise_application_error(-20000, 'You currently have 10 or more orders processing.');

I just wondered after search and trying many ways how to change the error messages for the other two errors or even not show them all together to the user?

Here is the code I've used

    create or replace trigger trg_order_limit
    before insert on placed_order for each row  
    declare
    v_count number;
    begin
   -- Get current order count
   select count(order_id)
   into   v_count
   from   placed_order
   where  fk1_customer_id = :new.fk1_customer_id;

   -- Raise exception if there are too many
   if v_count >= 10 then
   EXCEPTION
   WHEN OTHERS THEN
    raise_application_error(-20000, 'You currently have 10 or more orders  processing.');
   end if;
   end;

Thanks a lot Richard

Answer 1

The exception propagation goes from the internal-to-external block, as opposed to variable scope which goes from external-to-internal block. For more reference on this, read McLaughlin's "Programming with PL/SQL", Chapter 5.

What you are getting here is an exception stack - exceptions raised from the innermost blocks to the outermost blocks.

When you raise an exception from a trigger, your raise_application_error statement returns an error.

It is then propagated to the trigger block which says ORA-06512: at "C3283535.TRG_ORDER_LIMIT", line 12. This is because the trigger treats the raised exception as an error and stops to continue.

The error is then propagated to the session which raises the ORA-04088: error during execution of trigger 'C3283535.TRG_ORDER_LIMIT'. This error reports to us about where, as in which part of the program, the error was raised.

If you are using a front-end program like Java Server Pages or PHP, you will catch the raised error - 20000 first. So, you can display the same to your end user.

EDIT :

About the first error - ORA-20000, you can change it in the RAISE_APPLICATION_ERROR statement itself.

If you want to handle the ORA-06512, you can use Uday Shankar's answer which is helpful in taking care of this error and showing an appropriate error message.

But, you will still be getting the last ORA-04088. If I was at your place I wouldn't have worried, as after getting the ORA-20000 I would raise an application error at the front end itself while hiding all the other details from the user.

In fact, this is the nature of Oracle's exception stack. All the errors from the innermost to the outermost block are raised. This is helpful a lot of times for us to identify the exact source of error.

Answer 2

In the trigger you can add the exception handling part as shown below:

EXCEPTION
    WHEN OTHERS THEN
        raise_application_error(-20000, 'You currently have 10 or more orders processing.');

Answer 3

I see that this is quite an old post, but I think that readers should be aware that

1. This does not really enforce the business rule (max 10 orders). If is is just "some" number to avoid too high amounts and you don't care if sometimes people have 12 orders then this may be fine. But if not, think of a scenario where you have 9 orders already, and then orders for the same customer are inserted from 2 different sessions / transactions simultaneously. In that case you will end up with 11 orders, without detecting this overflow situation. So you can not rely on this trigger actually.
2. Besides that, you might need to have this trigger fire on update too, if the fk1_customer_id may be updated (I have seen implementations where at first a NULL is put into the FK column, and later being updated to the actual value). You may want to consider if this scenario is realistic.
3. There is a fundamental flaw in the trigger. You are inside a transaction and inside a statement that is currently being executed but not complete yet. So what if the insert is not a single row insert but something like insert into placed_order (select ... from waiting_orders ...) what do you expect the trigger to see?

This kind of business rule is not easy to enforce. But if you choose to do it in a trigger, you better do it in an after statement trigger (thus, not in a before row trigger). An after statement trigger still will not see results of other uncommitted transactions, but at least the current statement is in a defined state.

In fact the business rule CAN fundamentally only be enforced at commit time; but there is not a thing like an ON-COMMIT trigger in the Oracle database. What you can do is denormalising the count of records into the customers table (add a column ORDER_COUNT), and place a deferred constraint (ORDER_COUNT <= 10) in that table. But then you are still relying on correctly maintaining this field throughout your code.

A fully reliable alternative, but somewhat cumbersome, is to create a materialized view (something like SELECT fk_customer_id, count(*) order_count from placed_orders group by fk_customer_id, with FAST REFRESH ON COMMIT on the placed_order table and create a check constraint order_count <= 10 on the materialized view. This is about the only way to reliably enforce this type of constraints, without having to think of all possible situations like concurrent sessions, updates etc. Note however that the FAST REFRESH ON COMMIT will slow down your commit; so this solution is not useable for high volumes (sigh... Why just does Oracle not provide an ON COMMIT trigger...)