Oracle database allows invalid time portion of datetime column values under what circumstances?

Question

This case arises in a real-life situation where invalid data was in (and continuing to come into) an Oracle database which is extracted into a data processing system in Focus. Focus would choke and die on some rows with invalid time portions. The Oracle DBA would then go and copy the datetime into the affected column from a good column to allow the process to continue (yeah, I know).

I assisted troubleshooting the problem and found that in Oracle on an affected row:

DUMP(START_TIME)

gives:

'Typ=12 Len=7: 100,99,255,255,0,0,0'

While:

TO_CHAR(START_TIME, 'YYYY/MM/DD HH24:MI:SS')

gives:

ORA-01801: date format is too long for internal buffer

Looking at the DUMP() results, 'Typ=12 Len=7: 100,99,255,255,0,0,0', and the storage conventions, it appears that they are able to bypass the column's semantic limits and insert the equivalent of 0, -1, -1, -1, -1, -1, -1 or 0x00 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF

Which makes "sense", since 0xFF = 255 = -1 might potentially result in 0000/255/255 255:255:255 depending on how you interpret the bytes, signs and overflows.

Under what conditions (connection mechanism, etc) does Oracle allow invalid data to come into datetime columns?

Answer 1

I cannot seem to find the bug in a quick Metalink search (if you haven't opened a SR with Oracle yet, I'd suggest doing so), but I know there were bugs in the OCI layer (Oracle Call Interface) that allowed buggy code to insert invalid dates. For the most part, those bugs have been corrected for some time, though, so I would tend to wager that if you applied the latest patchset to the client machines that are doing the inserts that the problem would disappear.

Answer 2

Oracle generally doesn't allow it. The OCI layer can bypass the data type validation - Oracle basically trusts that what you put in those seven bytes make up a valid date. Similarly, at the OCI layer, Oracle can pass back seven bytes of 'date' and it is up to the client software to do whatever it wishes with those bytes (leave them as is, convert to a string or an epoch number...) Maybe a better approach is to work out what loads the data in there and see what that is doing with the dates.

If you are feeling exceptionally perverse you can use the undocumented REVERSE function on a date and make it invalid.