Page Comparison

...

This is possible by using the  _MINE_FIX_INVALID_DATE_NEW_VALUE setting.

For exampleExample: 

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 value can be obtained by dumping the target columns - eg. select DUMP(START_DATE,16) from table;

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

...

Ensure we have the _MINE_FIX_INVALID_DATE_NEW_VALUE set:

dbvrep> set _MINE_FIX_INVALID_DATE_NEW_VALUE = 77640101FFFFFF

The mine 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

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

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

TO_CHAR(END_DATE,'Y

-------------------

1900-01-01 21:46:14

1900-01-01 23:45:13

DUMP(END_DATE,16)

--------------------------------------------------------------------------------

Typ=12 Len=7: 77,64,1,1,16,2f,f

Typ=12 Len=7: 77,64,1,1,18,2e,e