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.

Monday, March 30, 2009

Recession garden

I do not remember which station I heard this on but their news teaser mentioned something about what to do for your "recession garden".

A what?

So because the United States is in a recession I am supposed to grow my own food in my own personal recession garden?

A what?

Do these people know how hard it is to maintain a garden large enough to supplement the store bought food supply a family requires to survive? Do they know how expensive it is? Hmm, I just lost my job and I am tapping my cash reserves to stay inside my house and keep my family fed. Wait, I got it. I will make a recession garden.

First I have no problem whatsoever with home gardens. If you truly want fresh tomatoes and other vegetables right out of the ground or off the vine you really cannot beat a home garden. Well that is not true. There are plenty of local farmers with farm markets where all sorts of freshness are available. I would rather help keep them in business because they serve a greater need.

Second, do these people live in Ohio? Residential soil is better suited for making pottery than growing things. You will likely need to borrow or rent a machine to grind up and process the soil and more than likely you will need to purchase a decent amount of top soil to grow stuff in.

Lastly, seed and chemicals for growth and parasite control are not free. Sanitization will also be required. Are there stray cats in the neighborhood? If so, you just built them another litter box so be prepared for that.

What is next? Recession Chia herb gardens? Recession barn so we can raise our own meat?