由於在整理資料時,不小心把prod上的好多workflow資料purge了.
現在想用db_link的方式把測試區的資料回寫.sql_script如下


declare

/*
create table lll02 as
select header_id,org_id,order_number ,oh.CREATION_DATE,oh.FLOW_STATUS_CODE,oh.ORDERED_DATE
from oe_order_headers_all@prod570 oh
where not exists (select 1 from wf_items@prod570 wi where wi.item_key=to_char(oh.header_id))
and oh.FLOW_STATUS_CODE in ('ENTER','ENTERED','BOOKED')
and oh.org_id in (2,3,4,201,202,203,98)
*/

cursor cur is

select to_char(header_id) header_id,org_id,order_number
from lll02@prod570 a
where 1=1
and exists (select count(*) from oe_order_lines_all@prod570
where header_id=a.header_id
and flow_status_code<>'CANCELLED'
and flow_status_code<>'CLOSED' )
and to_char(a.creation_date,'yyyy')='2006'
--and a.header_id=566426
and a.flow_status_code='BOOKED';

/*

delete WF_ITEMs@prod570
where item_key='566426'

delete WF_ITEM_ATTRIBUTE_VALUES@prod570
where item_key='566426'

delete WF_ITEM_ACTIVITY_STATUSES@prod570
where item_key='566426'

commit

*/

begin

for rec in cur loop

Insert into WF_ITEMS@prod570
(item_type, item_key, root_activity, root_activity_version, owner_role, begin_date, user_key)
select item_type, item_key, root_activity, root_activity_version, owner_role, begin_date, user_key
from wf_items
where item_key=rec.header_id;

commit;

Insert into WF_ITEM_ATTRIBUTE_VALUES@prod570
(item_type,item_key,name,text_value,number_value,date_value)
select item_type,item_key,name,text_value,number_value,date_value
from WF_ITEM_ATTRIBUTE_VALUES
where item_key=rec.header_id;

commit;

Insert into WF_ITEM_ACTIVITY_STATUSES@prod570
select * from WF_ITEM_ACTIVITY_STATUSES
where item_key=rec.header_id;

commit;

end loop;

end;


說明:當WF_ITEM_ATTRIBUTE_VALUES加上event_value時會出現如下提示.
ora-22804:物件表格或使用者定義類型的資料不允許遠端作業
為此我不寫此event_value

大家如有問題或建議,請留言.謝謝.