1 package body EDW_CLEAN_OBJECT as
2 /*$Header: EDWOCLNB.pls 120.1 2006/03/28 01:45:12 rkumar noship $*/
3
4 procedure clean_up_object(
5 Errbuf out NOCOPY varchar2,
6 Retcode out NOCOPY varchar2,
7 p_object_name in varchar2,
8 p_truncate_stg in varchar2) IS
9 Begin
10 retcode:='0';
11
12 g_object_name:=get_short_name_for_long(p_object_name);
13 if g_object_name is null then
14 errbuf:=g_status_message;
15 retcode:='2';
16 return;
17 end if;
18 g_truncate_stg:=p_truncate_stg;
19 EDW_OWB_COLLECTION_UTIL.setup_conc_program_log(g_object_name);
20 write_to_log_file('g_object_name='||g_object_name||',g_truncate_stg='||g_truncate_stg);
21 init_all;
22 if is_dimension(g_object_name) then
23 if clean_up_dimension(g_object_name)=false then
24 errbuf:=g_status_message;
25 retcode:='2';
26 return;
27 end if;
28 else
29 if clean_up_fact(g_object_name)=false then
30 errbuf:=g_status_message;
31 retcode:='2';
32 return;
33 end if;
34 end if;
35 Exception when others then
36 errbuf:=sqlerrm;
37 retcode:='2';
38 End;
39
40 function clean_up_dimension(p_dim varchar2) return boolean is
41 Begin
42 if read_metadata(p_dim)=false then
43 return false;
44 end if;
45 if clean_dim_objects=false then
46 return false;
47 end if;
48 return true;
49 Exception when others then
50 g_status_message:=sqlerrm;
51 g_status:=false;
52 return false;
53 End;
54
55
56 function read_metadata(p_dim varchar2) return boolean is
57 l_stmt varchar2(10000);
58 TYPE CurTyp IS REF CURSOR;
59 cv CurTyp;
60 l_table varchar2(400);
61 l_table_owner varchar2(30); --bug#4905343
62 l_level_tag varchar2(30); --bug#4905343
63 Begin
64 g_dim:=p_dim;
65 g_bis_owner:=EDW_OWB_COLLECTION_UTIL.get_db_user('BIS');
66 g_dim_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(g_dim);
67
68 l_level_tag:='_LTC';
69
70 l_stmt:='select syn.table_owner '||
71 ' from user_synonyms syn, edw_dimensions_md_v dim, edw_levels_md_v lvl'||
72 ' where dim.dim_name=:a and lvl.dim_id=dim.dim_id and syn.table_name=lvl.level_name||:b'; ----bug#4905343
73
74 open cv for l_stmt using g_dim,l_level_tag; --bug#4905343
75 fetch cv into l_table_owner;
76 close cv;
77
78 l_stmt:='select ltc.name,lstg.name,snplog.log_table from edw_tables_md_v ltc, edw_dimensions_md_v dim, '||
79 'edw_levels_md_v lvl, edw_tables_md_v lstg,all_snapshot_logs snplog,edw_pvt_map_properties_md_v map, '||
80 'user_synonyms syn '||
81 'where dim.dim_name=:a '||
82 'and lvl.dim_id=dim.dim_id and ltc.name=lvl.level_name||:b '|| --bug#4905343
83 'and map.primary_target(+)=ltc.elementid and map.primary_source=lstg.elementid(+) '||
84 'and snplog.master=ltc.name and snplog.log_owner=:c';
85
86 write_to_log_file('going to execute '||l_stmt);
87 open cv for l_stmt using g_dim, l_level_tag,l_table_owner; --bug#4905343
88 g_number_ltc:=1;
89 loop
90 fetch cv into g_ltc_tables(g_number_ltc),g_lstg_tables(g_number_ltc),g_ltc_snplogs(g_number_ltc);
91 exit when cv%notfound;
92 g_number_ltc:=g_number_ltc+1;
93 end loop;
94 g_number_ltc:=g_number_ltc-1;
95 g_number_op_tables:=0;
96 for i in 1..g_number_ltc loop
97 g_number_op_tables:=g_number_op_tables+1;
98 l_table:=substr(g_ltc_tables(i),1,26);
99 g_op_tables(g_number_op_tables):=l_table||'OK';
100 end loop;
101 g_dim_ilog:=g_dim||'IL';
102 return true;
103 Exception when others then
104 g_status_message:=sqlerrm;
105 g_status:=false;
106 return false;
107 End;
108
109 function clean_dim_objects return boolean is
110 l_stmt varchar2(4000);
111 l_count number;
112 Begin
113 l_stmt:='truncate table '||g_dim_owner||'.'||g_dim;
114 write_to_log_file(l_stmt);
115 if execute_stmt(l_stmt)=false then
116 null;
117 end if;
118 for i in 1..g_number_ltc loop
119 l_stmt:='truncate table '||g_dim_owner||'.'||g_ltc_tables(i);
120 write_to_log_file(l_stmt);
121 if execute_stmt(l_stmt)=false then
122 null;
123 end if;
124 end loop;
125 if g_truncate_stg='Y' then
126 for i in 1..g_number_ltc loop
127 if g_lstg_tables(i) is not null then
128 l_stmt:='truncate table '||g_dim_owner||'.'||g_lstg_tables(i);
129 write_to_log_file(l_stmt);
130 if execute_stmt(l_stmt)=false then
131 null;
132 end if;
133 end if;
134 end loop;
135 else
136 for i in 1..g_number_ltc loop
137 if g_lstg_tables(i) is not null then
138 l_stmt:='update '||g_lstg_tables(i)||' set collection_status=''READY'' where collection_status<>''READY'' '||
139 ' and rownum<=100000';
140 write_to_log_file(l_stmt);
141 loop
142 execute immediate l_stmt;
143 l_count:=sql%rowcount;
144 commit;
145 if l_count <100000 then
146 exit;
147 end if;
148 end loop;
149 end if;
150 end loop;
151 end if;
152
153 for i in 1..g_number_ltc loop
154 if g_ltc_snplogs(i) is not null then
155 l_stmt:='truncate table '||g_dim_owner||'.'||g_ltc_snplogs(i);
156 write_to_log_file(l_stmt);
157 if execute_stmt(l_stmt)=false then
158 null;
159 end if;
160 end if;
161 end loop;
162 l_stmt:='drop table '||g_bis_owner||'.'||g_dim_ilog;
163 write_to_log_file(l_stmt);
164 if execute_stmt(l_stmt)=false then
165 null;
166 end if;
167 for i in 1..g_number_op_tables loop
168 l_stmt:='drop table '||g_bis_owner||'.'||g_op_tables(i);
169 write_to_log_file(l_stmt);
170 if execute_stmt(l_stmt)=false then
171 null;
172 end if;
173 end loop;
174 return true;
175 Exception when others then
176 g_status_message:=sqlerrm;
177 g_status:=false;
178 return false;
179 End;
180
181 procedure write_to_log_file(p_message varchar2) is
182 Begin
183 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(p_message);
184 Exception when others then
185 null;
186 End;
187
188 function execute_stmt(p_stmt varchar2) return boolean is
189 Begin
190 execute immediate p_stmt;
191 return true;
192 Exception when others then
193 g_status_message:=sqlerrm;
194 g_status:=false;
195 return false;
196 End;
197
198 procedure init_all is
199 begin
200 g_status:=true;
201 End;
202
203 function is_dimension(p_object_name varchar2) return boolean is
204 l_stmt varchar2(2000);
205 TYPE CurTyp IS REF CURSOR;
206 cv CurTyp;
207 l_res number;
208 Begin
209 l_stmt:='select 1 from edw_dimensions_md_v where dim_name=:a';
210 write_to_log_file(l_stmt);
211 open cv for l_stmt using p_object_name;
212 fetch cv into l_res;
213 close cv;
214 if l_res=1 then
215 return true;
216 else
217 return false;
218 end if;
219 Exception when others then
220 g_status_message:=sqlerrm;
221 g_status:=false;
222 return false;
223 End;
224
225 function read_fact_metadata(p_fact varchar2) return boolean is
226 l_stmt varchar2(10000);
227 TYPE CurTyp IS REF CURSOR;
228 cv CurTyp;
229 l_owner varchar2(400);
230 Begin
231 g_bis_owner:=EDW_OWB_COLLECTION_UTIL.get_db_user('BIS');
232 g_dim_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(p_fact);
233 l_stmt:='select rel.name from edw_tables_md_v rel,edw_pvt_map_properties_md_v map,edw_facts_md_v tgt '||
234 'where tgt.fact_name=:a and map.primary_target=tgt.fact_id '||
235 'and rel.elementid=map.primary_source ';
236 write_to_log_file(l_stmt);
237 open cv for l_stmt using p_fact;
238 fetch cv into g_fstg_table;
239 close cv;
240 g_fact_snplog :=EDW_OWB_COLLECTION_UTIL.get_table_snapshot_log(p_fact);
241 g_fact_dlog:=EDW_OWB_COLLECTION_UTIL.get_log_for_table(p_fact,'Delete Log');
242 if g_fact_dlog is null then
243 g_fact_dlog:=g_bis_owner||'.'||substr(g_object_name,1,26)||'DLG';
244 else
245 l_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(g_fact_dlog);
246 g_fact_dlog:=l_owner||'.'||g_fact_dlog;
247 end if;
248 g_fact_ilog:=g_bis_owner||'.'||substr(p_fact,1,26)||'OK';
249 l_stmt:='select distinct cube_src.fact_id||''_''||cube.fact_id from edw_facts_md_v cube,edw_facts_md_v cube_src, '||
250 'edw_pvt_map_properties_md_v map where map.primary_target=cube.fact_id and map.primary_source=cube_src.fact_id '||
251 'and cube_src.fact_name=:a';
252 g_number_ilog:=1;
253 write_to_log_file(l_stmt);
254 open cv for l_stmt using p_fact;
255 loop
256 fetch cv into g_base_fact_ilog(g_number_ilog);
257 exit when cv%notfound;
258 g_number_ilog:=g_number_ilog+1;
259 end loop;
260 g_number_ilog:=g_number_ilog-1;
261 close cv;
262 for i in 1..g_number_ilog loop
263 g_base_fact_dlog(i):=g_bis_owner||'.D'||g_base_fact_ilog(i);
264 g_base_fact_ilog(i):=g_bis_owner||'.I'||g_base_fact_ilog(i);
265 end loop;
266 l_stmt:='select distinct cube_src.fact_id||''_''||cube.fact_id from edw_facts_md_v cube,edw_facts_md_v '||
267 'cube_src, edw_pvt_map_properties_md_v map where map.primary_source=cube.fact_id and '||
268 'map.primary_target=cube_src.fact_id '||
269 'and cube_src.fact_name=:a';
270 g_derv_number_ilog:=1;
271 write_to_log_file(l_stmt);
272 open cv for l_stmt using p_fact;
273 loop
274 fetch cv into g_derv_fact_ilog(g_derv_number_ilog);
275 exit when cv%notfound;
276 g_derv_number_ilog:=g_derv_number_ilog+1;
277 end loop;
278 g_derv_number_ilog:=g_derv_number_ilog-1;
279 close cv;
280 for i in 1..g_derv_number_ilog loop
281 g_derv_fact_dlog(i):=g_bis_owner||'.D'||g_derv_fact_ilog(i);
282 g_derv_fact_ilog(i):=g_bis_owner||'.I'||g_derv_fact_ilog(i);
283 end loop;
284
285 return true;
286 Exception when others then
287 g_status_message:=sqlerrm;
288 g_status:=false;
289 return false;
290 End;
291
292 function clean_fact_objects return boolean is
293 l_stmt varchar2(10000);
294 l_count number;
295 Begin
296 l_stmt:='truncate table '||g_dim_owner||'.'||g_object_name;
297 write_to_log_file(l_stmt);
298 if execute_stmt(l_stmt)=false then
299 null;
300 end if;
301 if g_fact_snplog is not null then
302 l_stmt:='truncate table '||g_dim_owner||'.'||g_fact_snplog;
303 write_to_log_file(l_stmt);
304 if execute_stmt(l_stmt)=false then
305 null;
306 end if;
307 end if;
308 if g_fact_dlog is not null then
309 l_stmt:='truncate table '||g_fact_dlog;
310 write_to_log_file(l_stmt);
311 if execute_stmt(l_stmt)=false then
312 null;
313 end if;
314 end if;
315 l_stmt:='drop table '||g_fact_ilog;
316 write_to_log_file(l_stmt);
317 if execute_stmt(l_stmt)=false then
318 null;
319 end if;
320 for i in 1..g_number_ilog loop
321 l_stmt:='drop table '||g_base_fact_dlog(i);
322 write_to_log_file(l_stmt);
323 if execute_stmt(l_stmt)=false then
324 null;
325 end if;
326 l_stmt:='drop table '||g_base_fact_ilog(i);
327 write_to_log_file(l_stmt);
328 if execute_stmt(l_stmt)=false then
329 null;
330 end if;
331 end loop;
332 for i in 1..g_derv_number_ilog loop
333 l_stmt:='drop table '||g_derv_fact_dlog(i);
334 write_to_log_file(l_stmt);
335 if execute_stmt(l_stmt)=false then
336 null;
337 end if;
338 l_stmt:='drop table '||g_derv_fact_ilog(i);
339 write_to_log_file(l_stmt);
340 if execute_stmt(l_stmt)=false then
341 null;
342 end if;
343 end loop;
344 if g_fstg_table is not null then
345 if g_truncate_stg='Y' then
346 l_stmt:='truncate table '||g_dim_owner||'.'||g_fstg_table;
347 write_to_log_file(l_stmt);
348 if execute_stmt(l_stmt)=false then
349 null;
350 end if;
351 else
352 l_stmt:='update '||g_fstg_table||' set collection_status=''READY'' where collection_status<>''READY'' '||
353 ' and rownum<=100000';
354 write_to_log_file(l_stmt);
355 loop
356 execute immediate l_stmt;
357 l_count:=sql%rowcount;
358 commit;
359 if l_count <100000 then
360 exit;
361 end if;
362 end loop;
363 end if;
364 end if;
365
366 return true;
367 Exception when others then
368 g_status_message:=sqlerrm;
369 g_status:=false;
370 return false;
371 End;
372
373
374 function clean_up_fact(p_fact varchar2) return boolean is
375 Begin
376 if read_fact_metadata(p_fact)=false then
377 return false;
378 end if;
379 if clean_fact_objects=false then
380 return false;
381 end if;
382 return true;
383 Exception when others then
384 g_status_message:=sqlerrm;
385 g_status:=false;
386 return false;
387 End;
388
389 function get_short_name_for_long(p_name varchar2) return varchar2 is
390 l_stmt varchar2(2000);
391 TYPE CurTyp IS REF CURSOR;
392 cv CurTyp;
393 l_name varchar2(400);
394 Begin
395 l_stmt:='select relation_name from edw_relations_md_v where relation_long_name=:a';
399 close cv;
396 write_to_log_file(l_stmt);
397 open cv for l_stmt using p_name;
398 fetch cv into l_name;
400 if l_name is null then
401 l_name:=p_name;
402 end if;
403 return l_name;
404 Exception when others then
405 g_status_message:=sqlerrm;
406 g_status:=false;
407 return null;
408 End;
409
410
411
412 END EDW_CLEAN_OBJECT;
[Home] [Help]