PL/SQL Stored Procedure EXCEPTION not working

Hi Experts,

I have a stored procedure I am writing for an on-line class and cannot figure out what I am doing wrong. I need the procedure to write to an error table "order_errors" for any error that occurs while trying to execute it.

The package is called "Process_Orders" and the procedure is called "Add_Order". There are three parameters for Add_Order: cno, eno, and received. A call with valid parameters works fine, but a call with "bad data" text where a date was expected and so on does not trigger the exception handler.

Here is the output from SQL PLUS when the number 23 is entered where a date should be:
SQL> execute Process_Orders.Add_Order(12,14,'23');
BEGIN Process_Orders.Add_Order(12,14,'23'); END;

                                                         
ERROR at line 1:
ORA-01840: input value not long enough for date format
ORA-06512: at line 1

CREATE OR REPLACE PACKAGE Process_Orders AS
	PROCEDURE Add_Order(p_cno IN orders.cno%TYPE,
			p_eno IN orders.eno%TYPE,
			p_received IN DATE);
			p_creation_date DATE;
			p_last_update_date DATE;
			p_created_by VARCHAR2(10);
			p_last_update_by VARCHAR2(10);	
			p_date DATE;
			error_message VARCHAR2(100);
END Process_Orders;
/
show errors
set echo on
 
 
CREATE OR REPLACE PACKAGE BODY Process_Orders AS
  -- Add a new order for the specified class.
  -- Order_number_seq should be used to populate the order number (ONO) column.
 
 
	PROCEDURE Add_Order(p_cno IN orders.cno%TYPE,
			p_eno IN orders.eno%TYPE,
			p_received IN DATE) AS
 
	BEGIN
  	INSERT INTO orders (ono,cno,eno,received,creation_date,created_by,last_update_date,last_update_by)
  	SELECT Order_number_seq.NextVal,p_cno,p_eno,NVL(p_received,CURRENT_DATE),CURRENT_DATE,
	USER,CURRENT_DATE,USER FROM DUAL;
  	COMMIT;
 
 	EXCEPTION
	WHEN OTHERS THEN
	error_message := sqlerrm(sqlcode);
	DBMS_OUTPUT.PUT_LINE (error_message);
	INSERT INTO order_errors (ono,transaction_date,message) 
	VALUES (Order_number_seq.NextVal,CURRENT_DATE,error_message);
	COMMIT;
	END Add_Order;
 
END Process_Orders;
/
show errors

Select allOpen in new window