PLSQL Package – Catching Errors in procedure (Oracle)

exceptionoracleplsql

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).

Best Answer

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>
Related Question