Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a problem with my package (and procedure) not catching exceptions.

I have the below code:

CREATE OR REPLACE PACKAGE process_orders
IS
    PROCEDURE add_order (
        order_num   NUMBER,
        cust_num    NUMBER,
        rec_date    DATE);
END process_orders; 
/

CREATE OR REPLACE PACKAGE BODY process_orders
IS
    PROCEDURE add_order (
        order_num   NUMBER,
        cust_num    NUMBER,
        rec_date    DATE)
        IS
        status      VARCHAR2(50);
    BEGIN
        INSERT INTO orders VALUES (order_num, cust_num,
            NULL, rec_date, NULL);
    EXCEPTION
        WHEN OTHERS THEN
            status := SUBSTR(SQLERRM,1,50);
            INSERT INTO orders_errors VALUES (SYSDATE,
                order_num, status);
    END;        
END process_orders;
/

If in sqlplus I execute: exec process_orders.add_order(2000,1/0,'25-DEC-91'); (notice the div by 0 error in the 2nd arg) I do not get anything inserted into the orders_errors table (and no errors except for the div by 0 error).

share|improve this question

1 Answer 1

up vote 3 down vote accepted

This is happening because the division by zero error is being raised before the procedure is even executed - It's happening when the arguments are actually passed in to the procedure.

You can verify this with a small proc that doesn't do anything:

create or replace procedure do_nothing(divbyzero number)
IS
BEGIN
  NULL;
END;
/

Test:

SQL> exec do_nothing(1/0);
BEGIN do_nothing(1/0); END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 1


SQL>
share|improve this answer
    
This was my problem. I also was testing the wrong number of arguments and it wasn't working (same idea). Sometimes its the simple things, I guess. –  Tgeo Oct 23 '12 at 1:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.