Tuesday, March 31, 2009

ORA-01839 using an INTERVAL

One of our application developers had a program that was been active and usually working for years but for some reason does not work issuing a cursor not open error every once in a while. It has been a source of anger for a while because the application will magically start working a day later without any changes to the application or host system. The source of the problem: an error occurred at cursor open due to date arithmetic using an INTERVAL and that error was not handled.

Shame on someone for not handling the error.

Regarding the INTERVAL logic, if you add or subtract a month interval from a date that results in the day being outside the number of days in the month (going from March with 31 to April with 30 for example) the statement will throw an error.

The developer is better off using the Oracle function ADD_MONTHS which does not throw an error. If the application needs a value of the first of the month if 31 days then the application should probably be subtracting days instead of months. If the warranty expires in 90 days then add 90 days. If the warranty expires in three months then unless there is some complex logic in the contract it should end on June 30 and not July 1 (just assuming).
SELECT SYSDATE FROM DUAL;
SYSDATE
---------
31-MAR-09

SELECT SYSDATE - INTERVAL '2' MONTH FROM DUAL;
SYSDATE-I
---------
31-JAN-09

SELECT SYSDATE - INTERVAL '4' MONTH FROM DUAL;
ORA-01839: date not valid for month specified

SELECT ADD_MONTHS(SYSDATE,-4) FROM DUAL;
ADD_MONTH
---------
30-NOV-08
The INTERVAL logic has been discussed elsewhere including a comment from Tom Kyte about the pure ANSI implementation. I document it here to spread the word and hopefully keep others from banging their heads into a frothy pulp over silly crap like this. Standards; gotta love them.

2 comments:

Anonymous said...

THANKS

Anonymous said...

Thanks, too.