Oracle date violation during upgrade

Question

I am transferring some data from one oracle oracle schema to a new schema / table in the same database.

The migration script does the following:

create table newtable as select ... cast(ACTIVITYDATE as date) as ACTIVITY_DATE, ... FROM oldtable where ACTIVITYDATE > sysdate - 1000; 

If I look at the source data, it looks great - here is one entry:

 select activitydate, to_char(activitydate, 'MON DD,YYYY'), to_char(activitydate, 'DD-MON-YYYY HH24:MI:SS'), dump(activitydate), length(activitydate) from orginaltable where oldpk = 1067514 

Result:

 18-NOV-10 NOV 18,2010 18-NOV-2010 12:59:15 Typ=12 Len=7: 120,110,11,18,13,60,16 

Transferred data indicating that the data is damaged:

 select activity_date, to_char(activity_date, 'MON DD,YYYY'), to_char(activity_date, 'DD-MON-YYYY HH24:MI:SS'), dump(activity_date), length(activity_date) from newtable where id = 1067514 

Result:

 18-NOV-10 000 00,0000 00-000-0000 00:00:00 Typ=12 Len=7: 120,110,11,18,13,0,16 

About 5,000 of the 350,000 records show this problem.

Can anyone explain how this happened?

Answer 1

UPDATE:

I do not find published links to this type of DATE corruption on the Oracle support site. (Maybe my quick searches just didn't open it.)

• Baddate Script Check database for corrupt dates [ID 95402.1]
• Error 2790435 - Serial INSERT with parallel SELECT conversion and type conversion can insert corrupted data [ID 2790435.8]

---

The result of the DUMP () function shows that the date value is indeed incorrect:

 Typ=12 Len=7: 120,110,11,18,13,0,16 

We expect that the minute byte should be a value from one to sixty, and not from zero.

7 bytes of DATE represent a century (+100), year (+100), month, day, hour (+1), minutes (+1), seconds (+1).

The only time I saw invalid DATE values ​​like this one was when a DATE value was provided as a bind variable from the Pro * C program (where the bind value is provided in an internal representation of 7 bytes, completely bypassing normal validation procedures that catch invalid dates, for example February 30)

There is no reason to expect the behavior you see, given the syntax of Oracle that you posted.

This is either a false anomaly (memory corruption?), Or if it is repeatable, then this is an error (error) in the Oracle code. If this is a flaw in Oracle code, the most likely suspect will be the "new" features in the non-partisan release.

(I know that CAST is a standard SQL function that has been used in other databases for centuries. I’m probably an old school and I never entered it into my repertoire of Oracle syntax. I don’t know which version of Oracle it introduced CAST, but I would stay with him in the first issue in which he appeared.)

---

Big "red flag" (as another comment noted): CAST( datecol AS DATE) .

You would expect the optimizer to consider this as equivalent to date_col ... but past experience shows us that TO_NUMBER( number_col ) actually interpreted by the optimizer as TO_NUMBER( TO_CHAR ( number_col ) ) .

I suspect something like this might happen to this unnecessary CAST.

---

Based on the one record you showed, I suspect that the problem is with values ​​with a value of "59" for minutes or seconds and possibly a value of "23" for several hours, errors will be displayed.

I would try checking places where minutes, hours or seconds are stored as 0:

 SELECT id, DUMP(activitydate) FROM newtable WHERE DUMP(activitydate) LIKE '%,0,%' OR DUMP(activitydate) LIKE '%,0' 

Answer 2

I saw similar things for spence7593, again with Pro * C. It is possible to create incorrect dates programmatically using the DBMS_STATS package. Not sure if there is a similar mechanism to undo this.

 create or replace function stats_raw_to_date (p_in raw) return date is v_date date; v_char varchar2(25); begin dbms_stats.CONVERT_RAW_VALUE(p_in, v_date); return v_date; exception when others then return null; end; / select stats_raw_to_date(utl_raw.cast_to_raw( chr(120)||chr(110)||chr(11)||chr(18)||chr(13)||chr(0)||chr(16))) from dual;