Dealing with invalid dates

In some cases the source application may insert an invalid date into an Oracle DATE field. 

For example a value of ’0000-00-00 00:00:00′  has been inserted into an Oracle DATE field. This is an invalid date as far as Oracle is concerned. This will be replicated into the target database as a NULL, but in some cases this may not be desirable. It maybe desirable to replace the NULL with a specific date.

This is possible by using the  _MINE_FIX_INVALID_DATE_NEW_VALUE setting.

For example: 

set _MINE_FIX_INVALID_DATE_NEW_VALUE = 77640101010101

This is the hex equivalent of 1900-01-01 00:00:00. If the source date is invalid, then the target date will be: 1900.01.01 00:00:00. For an update statement where the invalid date is part of the WHERE clause, the new value of date will also be used. In the following example END_DATE contained an invalid date value and has been changed to a valid date:

DELETE from AVI."INVALID_DATE"
where (1=1)
and PID = 101
and ORDER_NUMBER = 200
and START_DATE = 2012.09.15 15:24:26
and END_DATE = 1900.01.01 00:00:00

Invalid date with valid time component

In the case where the time component is valid, but the date component is not valid, the following can be set:

set _MINE_FIX_INVALID_DATE_NEW_VALUE = 77640101FFFFFF

This will keep the time component of the whole date but will only replace the date component with 1900.01.01.

Here is an example. We are replicating the whole schema called AVI.

Ensure we have the _MINE_FIX_INVALID_DATE_NEW_VALUE set:

dbvrep> set _MINE_FIX_INVALID_DATE_NEW_VALUE = 77640101FFFFFF

The mine process will have to be restarted after setting this to take affect.

Lets create a new table on the source database:

create table invalid_date (
pid number,
order_number number,
start_date date,
end_date date
);

Now lets insert an invalid date with a correct time component into this table:

export SOURCE_TNS=d112f

sqlplus -s avi/xxx@${SOURCE_TNS}<<SQL

truncate table invalid_date;

declare

   d date;

begin

dbms_stats.convert_raw_value('64640001182e0e', d);

insert into invalid_date values (601, 200, sysdate, d);

insert into invalid_date values (701, 200, sysdate, d);

end;

/

SQL

sqlplus -s avi/xxx@${SOURCE_TNS}<<SQL

declare

   d date;

begin

dbms_stats.convert_raw_value('64640001162f0f', d);

update invalid_date set end_date = d where pid = 601;

end;

/

SQL

sqlplus -s avi/xxx@${SOURCE_TNS}<<SQL

alter session set nls_date_format='YYYY.DD.MM HH24:MI:SS';

select PID, END_DATE from avi.invalid_date;

SQL

PID  END_DATE
---- -------------------
 601 0000.01.00 21:46:14
 701 0000.01.00 23:45:13