Oracle Scratchpad

August 18, 2009

Why test ?

Filed under: Troubleshooting — Jonathan Lewis @ 6:28 pm GMT Aug 18,2009

Here’s a simple problem that came up on the Oracle newsgroup a little while ago: how do you get the date for the last day of the previous month?

There are various ways that spring to mind but the simplest (and the first one I thought of) was – truncate the data to the start of month, then subtract one day:

select
        trunc(sysdate,'MM') - 1
from
        dual;

This is so obviously right and simple that (after running it once to make sure I had the syntax and spelling right) I probably wouldn’t even think about testing it; after all, testing has to stop somewhere!  (This explains the title of the piece – it’s not meant to be ironic, there really are times when there seems to be no need to test.)

The original question hadn’t been phrased quite the way I’ve put it, though. Their code was another obvious implementation, had been running for a couple of months, and had suddenly failed with Oracle error: “ORA-01839: date not valid for month specified”. The following code is what they had written, but I’ve also included a simplified version of the same thing:

SELECT
	TO_DATE(
		TO_CHAR(
			LAST_DAY(
				SYSDATE + numtoyminterval(-1,'MONTH')
			), 'YYYY-MM-DD'
		), 'YYYY-MM-DD'
	)
FROM DUAL
;

SELECT
	LAST_DAY(
		SYSDATE + numtoyminterval(-1,'MONTH')
	)
FROM DUAL
;

Try running it now, and there’s a very good chance you’ll get the right result – with something like a 2% chance of failure. Yet it’s so “obviously” right that it’s hard to think that it might be worth testing. We subtract one month from the current date and report the last day of the resulting month – what could be simpler ? (It was a rhetorical question but if you want a literal answer another way of saying the same thing in Oracle would be “last_day(add_months(sysdate + rownum,-1))” and that doesn’t go wrong.

There’s probably a Metalink note about this – but the problem appears when you try to subtract one month from a date in a month with more days than the previous month: the OP first hit his problem on 31st July when Oracle tried to generate the date “31st June”.

So I thought I’d write a little script to demonstrate the problem, just to show how easy it can be to test things; but when I started up an SQL*Plus session and ran the script I got an interesting result.

Imagine running the following script in early August – on what date should it fail ? I’ve shown three sets of results, which should tell you why the error message seems to appear at unexpected points:

select
	rownum,
	sysdate + rownum,
	last_day(
		(sysdate + rownum) + numtoyminterval(-1,'MONTH')
	)
from
	all_objects
where
	rownum <= 365
;

... rows omitted
default arraysize
        73 19-OCT-09 30-SEP-09
        74 20-OCT-09 30-SEP-09
        75 21-OCT-09 30-SEP-09
ERROR:
ORA-01839: date not valid for month specified

set arraysize 1
        81 27-OCT-09 30-SEP-09
        82 28-OCT-09 30-SEP-09
        83 29-OCT-09 30-SEP-09
ERROR:
ORA-01839: date not valid for month specified

set arraysize 2
        80 26-OCT-09 30-SEP-09
        81 27-OCT-09 30-SEP-09
        82 28-OCT-09 30-SEP-09
ERROR:
ORA-01839: date not valid for month specified

Starting from early August you probably worked out that the query would crash on 31st October. (31st Aug goes back to 31st July, which is valid; 30th Sept goes back to 30th Aug, which is valid; 31st Oct tries to go back to 31st Sept – oops). But the first time I ran the script it seemed to crash, as you can see, on 22nd October after reporting the correct result for 21st October.

This puzzled me for a moment – until I remembered array fetching. 21st Oct is the last row of the last successful array fetch, the query crashed part-way through the next array fetch – somewhere between 22nd Oct and 14 days later. Changing the arraysize changes the apparent crash point – and given the way that SQL*Plus plays a funny little game under the covers by adding one to the specified arraysize it’s not possible to see from SQL*Plus exactly what value the server is crashing on.

It’s strange the little side effects you see, even on the simplest of tests.

10 Comments »

  1. I remember first realizing the implications of using the interval functions when reading Tom Kyte’s Expert Oracle Database Architecture. He highlights this issue on pages 526 and 527.

    Thanks for reminding us all!

    Comment by Centinul — August 18, 2009 @ 6:58 pm GMT Aug 18,2009 | Reply

  2. add_months does not seem to suffer from the same problem:

    SQL> select add_months(last_day(sysdate),-1) from dual;

    ADD_MONTHS
    ———-
    2009-07-31

    1 row selected.

    SQL> select add_months(last_day(sysdate),-2) from dual;

    ADD_MONTHS
    ———-
    2009-06-30

    1 row selected.

    SQL> select add_months(last_day(date ‘2009-07-18′),-1) from dual;

    ADD_MONTHS
    ———-
    2009-06-30

    1 row selected.

    SQL>

    Comment by Wolfgang Breitling — August 18, 2009 @ 9:47 pm GMT Aug 18,2009 | Reply

  3. I need to do this a lot and always last_day(add_months(sysdate,-1)) — can’t go wrong with that one.

    Comment by richie — August 18, 2009 @ 10:26 pm GMT Aug 18,2009 | Reply

  4. I knew a developer who often said, “Testing? That means doing it all twice.”

    Comment by Nigel — August 20, 2009 @ 8:59 am GMT Aug 20,2009 | Reply

  5. So! You’ve met him! My favourite quote: “Execution Plan? That’s an index, right?”.

    Comment by Nigel — August 21, 2009 @ 8:56 am GMT Aug 21,2009 | Reply

  6. Jonathan,

    There was similar type of question posted in Orafaq.com http://www.orafaq.com/forum/t/149542/73596/ I am posting it here because, I am not sure as to why the procedure was not failing for Michel or for me.

    Can you please provide your invaluable advise on this?

    Ps. I participate in that forum with my name as bonker.

    Comment by Raj — August 31, 2009 @ 7:30 am GMT Aug 31,2009 | Reply

  7. [...] 5- How to get the date for the last day of the previous month? Jonathan Lewis – Why test ? [...]

    Pingback by Blogroll Report 14/08/2009 – 21/08/2009 « Coskan’s Approach to Oracle — September 7, 2009 @ 4:41 pm GMT Sep 7,2009 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,308 other followers