Friday, December 18, 2009

No code changed, but now ORA-01841. Mistery Solved

A report that is coded in PL/SQL that has existed for years "all of sudden" started generating a "ORA-01841: (full) year must be between -4713 and +9999, and not be 0" error. Why and why all of sudden?

I discovered that a data problem has existed for years. When reports run they save results to a shared database table. Later on in the reports, the reports delete some of the data that does not meet a given time period. The table has both YEAR and MONTH columns which are both numeric along with a unique primary key value generated from a sequence. You guessed it, the YEAR column for a different type of report for some reason had a zero in it. A DELETE statement in the report assembles a date value from the YEAR and MONTH values. With a value of zero, this caused the error.

What made it worse is I would then rerun the report and it would run through fine with no errors giving the correct expected values. What was happening behind the scenes was the report with the bad data had finished and deleted all its zero year rows out of the table by the time I reran the report. So the rerun went through fine with no errors.

That explained why the error was occurring, but not why it had never appeared before. The answer to this is our friend the Cost Based Optimizer (CBO). I had computed new statistics on the report table. With the prior statistics the CBO was applying a primary key WHERE clause criteria before assembling the date value from the YEAR and MONTH columns. So the report never hit the "zero year" rows from the other reports running at the same time. When I recomputed statistics on the report table it caused the CBO to change it access path and assemble the date value before applying the primary key restriction causing the Oracle error to appear. The old CBO statistics were masking the fact that there was a data error in the table i.e. a year value of 0.