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