I'm trying to restore a data from a backup data with some special condition for rows.
Example:
Today database in DB1:
City
ID Name location type
1 A [SDO.geom] 1
2 B [SDO.geom] 2
District
ID Name Atrr1 location Atrr2 type ...
1 AA <null> [sdo.geom] something 1
2 BB something [sdo.geom] <null> 2
3 CC something [sdo.geom] something 1
4 DD <null> [sdo.geom] something 1
2 day agos data in DB2:
City
ID Name location type
1 A [SDO.geom] 1
2 B [SDO.geom] 2
3 C [SDO.geom] 1
4 D [SDO.geom] 1
District
ID Name Atrr1 location Atrr2 type ...
1 AA some [sdo.geom] something 1
3 CC <null> [sdo.geom] <null> 1
I want to restore data 2 days ago with 2 conditions:
- only type=1 in every table
- If a field 2 days ago is null and now it has data, then it remain unchanged.
As far as I can go, I wrote a script using MINUS to return a table like (ID is the primary key of each object):
Table ID Action
City 3 "add"
City 4 "add"
District 1 "modify"
District 3 "modify" (but nothing change, because 2nd condition)
District 4 "remove"
I already wrote script to remove and add data from backup data. Then the modify is complicated. When using MINUS to compare table City and City@DB2_link I don't know which field is changed and there are some special fields such as SDO_geometry. So how can I write a script to update a whole rows with just table name and primary key (ID) from another database? Thank you.
Update, I added a test case script:
in database db1, current database
create table city (
ID NUMBER(5) PRIMARY KEY,
Name VARCHAR2(15),
Location SDO_GEOMETRY,
Type number(5));
create table district (
ID NUMBER(5) PRIMARY KEY,
Name VARCHAR2(15),
Location SDO_GEOMETRY,
Type number(5),
Attr1 varchar2(15),
Atrr2 varchar2(15)
);
insert into city values (1, 'A' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1);
insert into city values (2, 'B' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 2);
insert into district values (1, 'AA' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, null, 'something');
insert into district values (2, 'BB' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 2, 'something', null);
insert into district values (3, 'CC' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, 'something', 'something');
insert into district values (4, 'DD' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, null, 'something');
-- I build up another database db2 which is the back up from 2 day ago.
create table city (
ID NUMBER(5) PRIMARY KEY,
Name VARCHAR2(15),
Location SDO_GEOMETRY,
Type number(5));
create table district (
ID NUMBER(5) PRIMARY KEY,
Name VARCHAR2(15),
Location SDO_GEOMETRY,
Type number(5),
Attr1 varchar2(15),
Atrr2 varchar2(15)
);
insert into city values (1, 'A' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1);
insert into city values (2, 'B' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 2);
insert into city values (3, 'C' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1);
insert into city values (4, 'D' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1);
insert into district values (1, 'AA' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, 'something', 'something');
insert into district values (3, 'CC' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, null, null);
-- in db1 again:
CREATE PUBLIC DATABASE LINK db2_link connect to db2 identified by password using ip@10g;
-- I'm trying to restore all from db2 to db1 only where the type=1 in every tables, and: If a field currently have value in db1, db2 it was null, then it not change.(db2 not override that field on db1, example district id=3)
To help make things clear, I've loaded the original data into _bkp tables:
create table city (
ID NUMBER(5) PRIMARY KEY,
Name VARCHAR2(15),
Location SDO_GEOMETRY,
Type number(5));
create table district (
ID NUMBER(5) PRIMARY KEY,
Name VARCHAR2(15),
Location SDO_GEOMETRY,
Type number(5),
Attr1 varchar2(15),
Atrr2 varchar2(15)
);
insert into city values (1, 'A' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1);
insert into city values (2, 'B' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 2);
insert into district values (1, 'AA' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, null, 'something');
insert into district values (2, 'BB' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 2, 'something', null);
insert into district values (3, 'CC' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, 'something', 'something');
insert into district values (4, 'DD' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, null, 'something');
create table city_bkp (
ID NUMBER(5) PRIMARY KEY,
Name VARCHAR2(15),
Location SDO_GEOMETRY,
Type number(5));
create table district_bkp (
ID NUMBER(5) PRIMARY KEY,
Name VARCHAR2(15),
Location SDO_GEOMETRY,
Type number(5),
Attr1 varchar2(15),
Atrr2 varchar2(15)
);
insert into city_bkp values (1, 'A' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1);
insert into city_bkp values (2, 'B' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 2);
insert into city_bkp values (3, 'C' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1);
insert into city_bkp values (4, 'D' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1);
insert into district_bkp values (1, 'AA' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, 'something', 'something');
insert into district_bkp values (3, 'CC' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, null, null);
If you want to do a minus on SDO_geometry, you can extract the fields of it in your select to compare them:
select id, b.location.sdo_gtype, b.location.sdo_srid
from city_bkp b
minus
select id, c.location.sdo_gtype, c.location.sdo_srid
from city c;
ID LOCATION.SDO_GTYPE LOCATION.SDO_SRID
3 2003
4 2003
But it sounds like you don't really need to compare the individual fields. Just set the current table depending on whether the previous fields were null. I'm not clear if you want to do this at the field level or for the whole row.
Either way, merge can help.
In the update clause, if the old field is null and the new one isn't, set the value to the new field. Otherwise set it to the old one.
select * from district;
ID NAME LOCATION TYPE ATTR1 ATRR2
1 AA oracle.sql.STRUCT@3c61db2d 1 something
2 BB oracle.sql.STRUCT@38a44eec 2 something
3 CC oracle.sql.STRUCT@171bd0fb 1 something something
4 DD oracle.sql.STRUCT@a25192f 1 something
merge into district d
using ( select * from district_bkp where type = 1 ) b
on ( d.id = b.id )
when not matched then insert
values (b.id, b.name, b.location, b.type, b.attr1, b.atrr2 )
when matched then update
set d.name = case
when ( b.name is null and d.name is not null ) then d.name
else b.name
end,
d.location = case
when ( b.location is null and d.location is not null ) then d.location
else b.location
end,
d.attr1 = case
when ( b.attr1 is null and d.attr1 is not null ) then d.attr1
else b.attr1
end,
d.atrr2 = case
when ( b.atrr2 is null and d.atrr2 is not null ) then d.atrr2
else b.atrr2
end
where d.type = 1;
select * from district;
ID NAME LOCATION TYPE ATTR1 ATRR2
1 AA oracle.sql.STRUCT@539866f 1 something something
2 BB oracle.sql.STRUCT@1d66878a 2 something
3 CC oracle.sql.STRUCT@400e4669 1 something something
4 DD oracle.sql.STRUCT@39aa8c07 1 something
Merge also has a delete clause. So you can remove the rows from the current table too. Though you need a bit of jiggery-pokery to do this, as Lucas Jellema explains:
https://technology.amis.nl/2006/10/14/have-merge-remove-records-from-target-that-are-not-in-the-source-oracle-10g/ And of course, you're doing this over a DB link. So you'll hit:
merge into district d
using ( select * from district_bkp@loopback where type = 1 ) b
on ( d.id = b.id )
when not matched then insert
values (b.id, b.name, b.location, b.type, b.attr1, b.atrr2 )
when matched then update
set d.name = case
when ( b.name is null and d.name is not null ) then d.name
else b.name
end,
d.location = case
when ( b.location is null and d.location is not null ) then d.location
else b.location
end,
d.attr1 = case
when ( b.attr1 is null and d.attr1 is not null ) then d.attr1
else b.attr1
end,
d.atrr2 = case
when ( b.atrr2 is null and d.atrr2 is not null ) then d.atrr2
else b.atrr2
end
where d.type = 1;
SQL Error: ORA-22804: remote operations not permitted on object tables or user-defined type columns
You can get around this by making a local (materialized) view over the top of the remote table. So the statement only works on local objects:
create or replace view district_bkp_v as
select * from district_bkp@loopback;
merge into district d
using ( select * from district_bkp_v where type = 1 ) b
on ( d.id = b.id )
when not matched then insert
values (b.id, b.name, b.location, b.type, b.attr1, b.atrr2 )
when matched then update
set d.name = case
when ( b.name is null and d.name is not null ) then d.name
else b.name
end,
d.location = case
when ( b.location is null and d.location is not null ) then d.location
else b.location
end,
d.attr1 = case
when ( b.attr1 is null and d.attr1 is not null ) then d.attr1
else b.attr1
end,
d.atrr2 = case
when ( b.atrr2 is null and d.atrr2 is not null ) then d.atrr2
else b.atrr2
end
where d.type = 1;