...
This is possible by using the _MINE_FIX_INVALID_DATE_NEW_VALUE setting.
For exampleExample:
| No Format |
|---|
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:
...
In the case where the time component is valid, but the date component is not valid, the following can be set:
| No Format |
|---|
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.
...
| No Format | ||
|---|---|---|
| ||
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:
...
We will also update the row with PID=601:
| No Format | ||
|---|---|---|
| ||
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 |
Now lets select from this table to see what we have at the source database:
| No Format | ||
|---|---|---|
| ||
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 |
The result from the source table is as follows:
| No Format | ||
|---|---|---|
| ||
PID END_DATE ---- ------------------- 601 0000.01.00 21:46:14 701 0000.01.00 23:45:13 |
We can see that the date component is invalid, but the time component is correct.
TARGET
Dbvisit Replicate will replicate the changes and substitute them based on _MINE_FIX_INVALID_DATE_NEW_VALUE.
| No Format |
|---|
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 |
We see that the time component has been kept, but the date component has been set to 1900-01-01.