Raise different error (invalid date) in trigger ? 1

[saustin]

Hi,
The code below accomplishes the mission of preventing a user from entering an invalid date BUT the error raised is the standard ....

ORA-01858: a non-numeric character was found where a numeric was expected ORA-06512: at "SYSADM.BADUSER1DATE", line 6 ORA-04088: error during execution of trigger 'SYSADM.BADUSER1DATE'

Instead of the error message I want to display. Is there any work-around for this.

Many thanks for any suggestions, Steve.


DECLARE l_test DATE;

BEGIN

IF :NEWDATA.USER_1 > ' ' Then
l_test := TO_DATENEWDATA.USER_1, 'MM/DD/YYYY');
END IF;
EXCEPTION
WHEN VALUE_ERROR THEN
raise_application_error(-20000, 'Contract Date is not a Valid Date');
END;

 

[taixut]

Is not VALUE_ERROR... if you change it by OTHERS you'll see it returns back your message. Hte error is ORA-01858: a non-numeric character was found where a numeric was expected, but i dont know the exception name.

Hope it helps

 

[saustin]

Genius taixut ! Replacing VALUE_ERROR with WHEN OTHERS THEN got it done. Thanks so much ! Steve.