1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
| CREATE OR REPLACE PROCEDURE p_archive_test(piv_owner varchar2)
IS
lv_stmt varchar2(2000) := 'insert /*+ append */ into TABLE1 (COLUMNS)
select INS_COLUMNS
from TABLE2 tab2@db1
where not exists (select null
from TABLE1 tab1
where PKCOLUMNS
)
';
cursor c_tab IS
SELECT tab.table_name,'ERR$_'||tab.table_name ERR_TABLE
FROM all_tables tab
WHERE tab.owner = piv_owner
-- and tab.table_name = 'action'
--order by atb.TABLE_ORDER
;
-- For each table get the primary key columns
cursor c_pk(civ_table_name IN all_tables.TABLE_NAME%type)
IS
SELECT acl.COLUMN_NAME
FROM all_cons_columns acl
,all_constraints acn
WHERE acn.OWNER = piv_owner
AND acn.CONSTRAINT_TYPE = 'P'
AND acn.TABLE_NAME = civ_table_name
AND acl.OWNER = acn.OWNER
AND acl.TABLE_NAME = acn.table_name
AND acl.CONSTRAINT_NAME = acn.CONSTRAINT_NAME
ORDER BY acl.POSITION;
-- For each table get the corresponding table columns names
cursor c_ins_cols(civ_table_name IN all_tab_columns.TABLE_NAME%type)
IS
SELECT 'tab2.'||atc.COLUMN_NAME column_name
FROM all_tab_columns atc
WHERE atc.OWNER = piv_owner
AND atc.TABLE_NAME = civ_table_name
ORDER BY atc.COLUMN_ID;
-- For each table get the columns names excluding the primary key columns
cursor c_upd_cols(civ_table_name IN all_tab_columns.TABLE_NAME%type)
IS
SELECT 'tab1.'||atc.COLUMN_NAME||'=tab2.'||atc.column_name column_name
FROM all_tab_columns atc
WHERE atc.OWNER = piv_owner
AND atc.TABLE_NAME = civ_table_name
AND NOT EXISTS (SELECT 1
FROM all_cons_columns acl
,all_constraints acn
WHERE acl.OWNER = atc.owner
AND acl.TABLE_NAME = atc.TABLE_NAME
AND acl.column_name = atc.column_name
AND acn.OWNER = acl.OWNER
AND acn.TABLE_NAME = acl.TABLE_NAME
AND acn.constraint_type = 'P')
ORDER BY atc.COLUMN_ID;
-- For each table get the columns names
cursor c_cols(civ_table_name IN all_tab_columns.COLUMN_NAME%type)
IS
SELECT
--'tab1.'||
atc.COLUMN_NAME column_name
FROM all_tab_columns atc
WHERE atc.owner = piv_owner
AND atc.TABLE_NAME = civ_table_name
ORDER BY atc.COLUMN_ID;
lv_cols varchar2(4000);
lv_pk_cols varchar2(4000);
lv_ins_cols varchar2(4000);
lv_upd_cols varchar2(4000);
BEGIN
FOR r_tab IN c_tab
loop
lv_cols := '';
lv_pk_cols := '';
lv_ins_cols := '';
lv_upd_cols := '';
FOR r_pk IN c_pk(civ_table_name => r_tab.table_name)
loop
lv_pk_cols := lv_pk_cols||'tab1.'||r_pk.column_name||'=tab2.'||r_pk.column_name||' and ';
end loop r_pk_loop;
lv_pk_cols := substr(str1 => lv_pk_cols
,pos => 1
,len => length(ch => lv_pk_cols) - 5);
FOR r_ins_cols IN c_ins_cols(civ_table_name => r_tab.table_name)
loop
lv_ins_cols := lv_ins_cols||r_ins_cols.column_name||',';
end loop r_ins_cols_loop;
lv_ins_cols := substr(str1 => lv_ins_cols
,pos => 1
,len => length(ch => lv_ins_cols) - 1);
FOR r_upd_cols IN c_upd_cols(civ_table_name => r_tab.table_name)
loop
lv_upd_cols := lv_upd_cols||r_upd_cols.column_name||',';
end loop r_upd_cols_loop;
lv_upd_cols := substr(str1 => lv_upd_cols
,pos => 1
,len => length(ch => lv_upd_cols) - 1);
FOR r_cols IN c_cols(civ_table_name => r_tab.table_name)
loop
lv_cols := lv_cols||r_cols.column_name||',';
end loop r_cols_loop;
lv_cols := substr(str1 => lv_cols
,pos => 1
,len => length(ch => lv_cols) - 1);
lv_stmt := REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(lv_stmt
,'TABLE1'
,r_tab.table_name)
,'TABLE2'
,'DIST_'||r_tab.table_name)
,'PKCOLUMNS'
,lv_pk_cols)
,'UPD_COLUMNS'
,lv_upd_cols)
,'INS_COLUMNS'
,lv_ins_cols)
,'COLUMNS'
,LV_COLS)
,'TABLE3'
,R_TAB.ERR_TABLE);
-- here It highy advisable to store the sql statement that will be submitted
-- to the SQL engine before executing it dynamically
INSERT INTO t_sql_statement VALUES (lv_stmt);
execute immediate lv_stmt;
end loop ;
commit;
exception
when others then
rollback;
raise;
end p_archive_test; |
