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