...
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| 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 |
|
|
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 date but will only replace the date component with 1900.01.01.
Invalid date with valid time component
...
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| dbvrep> set _MINE_FIX_INVALID_DATE_NEW_VALUE = 77640101FFFFFF |
|
|
...
The mine process will have to be restarted after setting this to take affect.
...
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| create table invalid_date (
pid number,
order_number number,
start_date date,
end_date date
); |
|
|
Now lets insert some an invalid date with a correct time component into this table:
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| export SOURCE_TNS=d112f sqlplus -s avi/xxx@${SOURCE_TNS}<<SQLtruncate 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 |
|
|
We will also update the row with PID=601:
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| sqlplus -s avi/xxx@${SOURCE_TNS}<<SQLdeclare 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:
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| sqlplus -s avi/xxx@${SOURCE_TNS}<<SQLalter 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:
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| 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.
Now lets see what the actual result is on the TARGET database:
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| 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.