Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01841 error in trigger date validation.
ORA-01841 error in trigger date validation. [message #195022] Tue, 26 September 2006 11:21 Go to next message
saustin
Messages: 43
Registered: June 2003
Member
Hi,
When running the folling in a trigger to make sure the date value in a date field is valid ....


DECLARE l_test DATE;
bad_year EXCEPTION;

BEGIN

IF :NEWDATA.DESIRED_RECV_DATE > ' ' Then

if TO_CHAR(:NEWDATA.DESIRED_RECV_DATE,'DD-MM-YYYY') > '01-01-2100' OR TO_CHAR(:NEWDATA.DESIRED_RECV_DATE,'DD-MM-YYYY') < '01-01-2000' THEN
raise bad_year;
end if;
END IF;
EXCEPTION
WHEN bad_year Then
raise_application_error(-220000, 'Desired Receive Date is not a Valid Date');
END;


we compile ok but get an ORA-01841: (full) year must be between -4713 and +9999, and not be 0 ORA-06512: at "SYSADM.BADDESIREDRECVDATE", line 6 ORA-04088: error during execution of trigger 'SYSADM.BADDESIREDRECVDATE' error at runtime. Any ideas on this greatly appreciated !!!

Thanks, Steve.
Re: ORA-01841 error in trigger date validation. [message #195023 is a reply to message #195022] Tue, 26 September 2006 11:47 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, I'd rather see this

TO_CHAR(:NEWDATA.DESIRED_RECV_DATE,'DD-MM-YYYY') > '01-01-2100'

written vice versa:

:NEWDATA.DESIRED_RECV_DATE > TO_DATE('01.01.2100', 'dd.mm.yyyy')

BTW, "newdata" stands for "new", I suppose?

Furthermore, ":NEWDATA.DESIRED_RECV_DATE > ' '" should be ":NEWDATA.DESIRED_RECV_DATE IS NULL".

Where do you use "l_test"? It is declared, but never used.

Perhaps it would be a good idea to include the whole trigger, not just the part of it.
Re: ORA-01841 error in trigger date validation. [message #195036 is a reply to message #195023] Tue, 26 September 2006 12:46 Go to previous message
saustin
Messages: 43
Registered: June 2003
Member
Thanks for the reply Littlefoot.

This was the whole trigger.

...Furthermore, ":NEWDATA.DESIRED_RECV_DATE > ' '" should be ":NEWDATA.DESIRED_RECV_DATE IS NULL".

Genius !!!! had copied this from date validation on text field trigger and should NOT have been used with a date field. Changed and no more error.

Many thanks, Steve.
Previous Topic: Loading data into CSV files
Next Topic: Procedure runs slower in 10g
Goto Forum:
  


Current Time: Fri Dec 09 04:11:31 CST 2016

Total time taken to generate the page: 0.17498 seconds