· 6 years ago · Sep 20, 2019, 05:04 PM
1CREATE OR REPLACE PROCEDURE compare_schemes_3(dev_scheme_name VARCHAR2, prod_scheme_name VARCHAR2) IS
2 TYPE table_info_rec IS record(created DATE, tid INT, name_ VARCHAR2(32000));
3 TYPE table_info IS TABLE of table_info_rec;
4 TYPE cons_info_rec IS record(from_ VARCHAR2(30000), to_ VARCHAR2(30000), col VARCHAR2(30000), tid INT);
5 TYPE cons_info IS TABLE of cons_info_rec;
6 TYPE table_varray IS varray(10000) of table_info_rec;
7 TYPE func_drop_rec IS record(name_ VARCHAR2(30000), type_ VARCHAR2(30000));
8 TYPE func_drop IS TABLE of func_drop_rec;
9
10 TYPE names IS TABLE of VARCHAR2(30000);
11
12 dev_funcs names;
13 prod_funcs func_drop;
14 dev_text names;
15 prod_text names;
16 diff_funcs names;
17
18 drop_prod names;
19 drop_cons names;
20 str VARCHAR2(100);
21 script clob;
22
23 dev table_info;
24 dev_cons cons_info;
25 prod table_info;
26 prod_cons cons_info;
27
28 not_exists table_info;
29 check_ table_info;
30
31 cnt_dev INT;
32 cnt_prod INT;
33 cnt_common INT;
34
35 arr table_varray;
36 tmp table_info_rec;
37BEGIN
38
39 script := '';
40-- all devs (double check)
41 SELECT created, object_id "tid", object_name "name_"
42 INTO dev
43 FROM dba_objects
44 WHERE object_type='TABLE' AND owner=dev_scheme_name
45 ORDER BY created;
46-- all prod
47 SELECT created, object_id "tid", object_name "name_"
48 INTO prod
49 FROM dba_objects
50 WHERE object_type='TABLE' AND owner=prod_scheme_name
51 ORDER BY created;
52
53-- not in prod
54 SELECT dev.* INTO not_exists FROM
55 dev LEFT JOIN prod ON prod.name_=dev.name_ WHERE prod.name_ IS NULL;
56-- not in dev
57 SELECT prod.name_ INTO drop_prod FROM
58 dev RIGHT JOIN prod ON prod.name_=dev.name_ WHERE dev.name_ IS NULL;
59
60-- fk in dev
61 SELECT cons.TABLE_NAME "from_", cons_pk.TABLE_NAME "to_", cons_cols.column_name "col", obj.object_id "tid"
62 INTO dev_cons
63 FROM dba_objects obj
64 LEFT JOIN all_cons_columns cons_cols ON cons_cols.table_name=obj.object_name AND cons_cols.owner=obj.owner
65 LEFT JOIN all_constraints cons ON cons.CONSTRAINT_NAME=cons_cols.CONSTRAINT_NAME AND cons.owner=obj.owner
66 LEFT JOIN all_constraints cons_pk ON cons_pk.CONSTRAINT_NAME=cons.R_CONSTRAINT_NAME AND cons_pk.OWNER=obj.owner
67 WHERE object_type='TABLE' AND cons.CONSTRAINT_TYPE='R' AND obj.owner=dev_scheme_name;
68-- fk in prod
69 SELECT cons.TABLE_NAME "from_", cons_pk.TABLE_NAME "to_", cons_cols.column_name "col", obj.object_id "tid"
70 INTO prod_cons
71 FROM dba_objects obj
72 LEFT JOIN all_cons_columns cons_cols ON cons_cols.table_name=obj.object_name AND cons_cols.owner=obj.owner
73 LEFT JOIN all_constraints cons ON cons.CONSTRAINT_NAME=cons_cols.CONSTRAINT_NAME AND cons.owner=obj.owner
74 LEFT JOIN all_constraints cons_pk ON cons_pk.CONSTRAINT_NAME=cons.R_CONSTRAINT_NAME AND cons_pk.OWNER=obj.owner
75 WHERE object_type='TABLE' AND cons.CONSTRAINT_TYPE='R' AND obj.owner=prod_scheme_name;
76
77-- common tables
78 SELECT dev.* INTO check_ FROM
79 dev INNER JOIN prod ON prod.name_=dev.name_;
80
81-- check diff in common tables
82 IF check_.count > 0 THEN
83 FOR i IN check_.first .. check_.last LOOP
84 SELECT COUNT(*) INTO cnt_dev FROM dev_cons WHERE from_=check_(i).name_;
85
86 SELECT COUNT(*) INTO cnt_prod FROM prod_cons WHERE from_=check_(i).name_;
87
88 IF cnt_dev = cnt_prod THEN
89 SELECT COUNT(*) INTO cnt_prod FROM dev_cons full JOIN prod_cons ON
90 dev_cons.from_=prod_cons.from_ AND dev_cons.to_=prod_cons.to_ AND dev_cons.col=prod_cons.col
91 WHERE dev_cons.from_=check_(i).name_ AND prod_cons.from_=check_(i).name_;
92
93 IF cnt_dev = cnt_prod THEN
94 SELECT COUNT(*) INTO cnt_dev FROM all_tab_columns
95 WHERE owner=dev_scheme_name AND table_name=check_(i).name_;
96 SELECT COUNT(*) INTO cnt_prod FROM
97 (SELECT * FROM all_tab_columns WHERE owner=dev_scheme_name AND table_name=check_(i).name_) d full JOIN
98 (SELECT * FROM all_tab_columns WHERE owner=prod_scheme_name AND table_name=check_(i).name_) p
99 ON d.column_name=p.column_name AND d.data_type=p.data_type AND d.nullable=p.nullable;
100 IF cnt_dev = cnt_prod THEN
101 CONTINUE;
102 END IF;
103 END IF;
104 END IF;
105
106 insert INTO not_exists values (check_(i));
107
108 END LOOP;
109 END IF;
110
111-- move not exists in arr
112 IF not_exists.count > 0 THEN
113 arr := table_varray();
114 arr.extend(not_exists.count);
115 FOR i IN not_exists.first .. not_exists.last LOOP
116 arr(i) := not_exists(i);
117 END LOOP;
118 END IF;
119-- circular deps
120 IF not_exists.count > 1 THEN
121 FOR qqq IN not_exists.first .. not_exists.last LOOP
122 FOR i IN 2 .. not_exists.last LOOP
123 SELECT COUNT(*) INTO cnt_dev FROM dev_cons WHERE "to_"=arr(i).name_ AND from_=arr(i-1).name_;
124
125 IF cnt_dev > 0 THEN
126 tmp := arr(i);
127 arr(i) := arr(i-1);
128 arr(i-1) := tmp;
129 END IF;
130 END LOOP;
131 END LOOP;
132
133 FOR i IN 2 .. not_exists.last LOOP
134 SELECT COUNT(*) INTO cnt_dev FROM dev_cons WHERE "to_"=arr(i).name_ AND from_=arr(i-1).name_;
135
136 IF cnt_dev > 0 THEN
137 dbms_output.put_line('Circular links!');
138 EXIT;
139 END IF;
140 END LOOP;
141 END IF;
142
143-- drop prod tables
144 IF drop_prod.count > 0 THEN
145 FOR i IN drop_prod.first .. drop_prod.last LOOP
146 SELECT f.constraint_name INTO drop_cons
147 FROM all_constraints f INNER JOIN all_constraints p ON p.constraint_name=f.r_constraint_name
148 WHERE f.constraint_type='R' AND p.owner=prod_scheme_name AND f.owner=prod_scheme_name AND p.table_name=drop_prod(i);
149 IF drop_cons.count > 0 THEN
150 FOR j IN drop_cons.first .. drop_cons.last LOOP
151 script := script || 'alter TABLE ' || drop_prod(i) || ' drop constraint ' || drop_cons(j) || '; ';
152 END LOOP;
153 END IF;
154 script := script || 'drop TABLE ' || drop_prod(i) || '; ';
155 END LOOP;
156 END IF;
157
158-- create in prod
159 IF not_exists.count > 0 THEN
160 dbms_output.put_line('TABLES:');
161 FOR i IN 1 .. not_exists.count LOOP
162 dbms_output.put_line(arr(i).name_);
163 SELECT script || REPLACE(dbms_metadata.get_ddl('TABLE', not_exists(i), dev_scheme_name), '"' || dev_scheme_name || '".', '') INTO script FROM dual;
164 END LOOP;
165 END IF;
166
167 SELECT name INTO dev_funcs
168 FROM all_source WHERE (type='FUNCTION' OR type='PROCEDURE') AND owner=dev_scheme_name group by name;
169
170 SELECT p.name name_, p.type type_ INTO prod_funcs
171 FROM (SELECT name, type FROM all_source WHERE (type='FUNCTION' OR type='PROCEDURE') AND owner=dev_scheme_name group by name, type) d
172 RIGHT JOIN (SELECT name, type FROM all_source WHERE (type='FUNCTION' OR type='PROCEDURE') AND owner=prod_scheme_name group by name, type) p
173 ON p.name=d.name AND p.TYPE=d.type WHERE d.name IS NULL;
174
175 IF prod_funcs.count > 0 THEN
176 FOR i IN prod_funcs.first .. prod_funcs.last LOOP
177 script := script || 'drop ' || prod_funcs(i).type_ || ' ' || prod_funcs(i).name_ || ';';
178 END LOOP;
179 END IF;
180
181 diff_funcs := names();
182
183 IF dev_funcs.count > 0 THEN
184 FOR i IN dev_funcs.first .. dev_funcs.last LOOP
185 SELECT text INTO dev_text FROM all_source
186 WHERE (TYPE='FUNCTION' OR TYPE='PROCEDURE') AND owner=dev_scheme_name AND name=dev_funcs(i)
187 ORDER BY line;
188 SELECT text INTO prod_text FROM all_source
189 WHERE (TYPE='FUNCTION' OR TYPE='PROCEDURE') AND owner=prod_scheme_name AND name=dev_funcs(i)
190 ORDER BY line;
191
192 IF prod_text.count != dev_text.count THEN
193 diff_funcs.extend;
194 diff_funcs(diff_funcs.last) := dev_funcs(i);
195 CONTINUE;
196 END IF;
197
198 FOR s IN prod_text.first .. prod_text.last LOOP
199 IF prod_text(s) != dev_text(s) THEN
200 diff_funcs.extend;
201 diff_funcs(diff_funcs.last) := dev_funcs(i);
202 -- EXIT;
203 END IF;
204 END LOOP;
205
206 END LOOP;
207 END IF;
208
209 IF diff_funcs.count > 0 THEN
210 dbms_output.put_line('FUNCTIONS AND PROCEDURES:');
211 FOR i IN diff_funcs.first .. diff_funcs.last LOOP
212 dbms_output.put_line(diff_funcs(i));
213 SELECT type INTO str FROM all_source WHERE owner=dev_scheme_name AND name=diff_funcs(i) AND (type='FUNCTION' OR type='PROCEDURE') group by name, type;
214 SELECT script || REPLACE(dbms_metadata.get_ddl(str, dev_scheme_name, diff_func(i)), '"' || dev_scheme_name || '".', '') INTO script FROM dual;
215 END LOOP;
216 END IF;
217
218 dbms_output.put_line(script);
219
220END;