1 package body EDW_SRC_DANG_RECOVERY as
2 /*$Header: EDWSRDTB.pls 115.6 2004/02/13 05:10:17 smulye noship $*/
3
4 /*will be called from setup in collection util
5 if p_db_link is null, then it assumes that source and warehouse are the same
6 */
7 function get_dangling_keys(p_dim_name varchar2,p_db_link varchar2,
8 p_pk_view varchar2,p_missing_key_view varchar2) return boolean is
9 Begin
10 g_object_name:=p_dim_name;
11 g_db_link:=p_db_link;
12 if p_missing_key_view is null then
13 write_to_log_file_n('Missing key view not specified for '||p_dim_name);
14 return true;
15 end if;
16 --if EDW_COLLECTION_UTIL.SOURCE_SAME_AS_TARGET then
17 if g_db_link is null then
18 g_src_same_wh_flag:=true;
19 g_db_link_stmt:=null;
20 write_to_log_file_n('Source and warehouse same');
21 else
22 g_src_same_wh_flag:=false;
23 g_db_link_stmt:='@'||g_db_link;
24 write_to_log_file_n('Source and warehouse different');
25 end if;
26 g_object_id:=get_dim_id(g_object_name);
27 g_missing_key_view:=p_missing_key_view;
28 g_pk_view:=p_pk_view;
29 write_to_log_file_n('Object name='||g_object_name||', ID='||g_object_id||', DB link='||g_db_link);
30 write_to_log_file_n('DB Link stmt '||g_db_link_stmt);
31 write_to_log_file('Missing Key view='||g_missing_key_view||',PK View='||g_pk_view);
32 if init_all=false then
33 return false;
34 end if;
35 if g_auto_dang_flag=false then
36 return true;
37 end if;
38 if get_dangling_keys=false then
39 return false;
40 end if;
41 return true;
42 Exception when others then
43 g_status_message:=sqlerrm;
44 write_to_log_file_n(g_status_message);
45 return false;
46 End;
47
48 function get_dangling_keys return boolean is
49 Begin
50 if check_table(g_wh_dang_table||g_db_link_stmt)=false then
51 if g_debug then
52 write_to_log_file_n('Remote '||g_wh_dang_table||' does not exist. No dangling processing to do!');
53 end if;
54 return true;
55 end if;
56 if get_ll_keys_from_wh=false then
57 return false;
58 end if;
59 if get_hl_keys_from_view=false then
60 return false;
61 end if;
62 if create_missing_key_view=false then
63 return false;
64 end if;
65 return true;
66 Exception when others then
67 g_status_message:=sqlerrm;
68 write_to_log_file_n(g_status_message);
69 return false;
70 End;
71
72 function get_ll_keys_from_wh return boolean is
73 l_stmt varchar2(20000);
74 l_table varchar2(200);
75 l_table2 varchar2(200);
76 l_table3 varchar2(200);
77 l_db_columns varcharTableType;
78 l_number_db_columns number;
79 l_found boolean;
80 Begin
81 if drop_table(g_level_table)=false then
82 null;
83 end if;
84 l_stmt:='create table '||g_level_table||' tablespace '||g_src_op_table_space;
85 l_stmt:=l_stmt||' as select upper(ltc.name) name,ltc.elementid id,upper(lvl.LEVEL_PREFIX) prefix from ';
86 l_stmt:=l_stmt||' edw_tables_md_v'||g_db_link_stmt||' ltc,edw_levels_md_v'||g_db_link_stmt||' lvl '||
87 'where lvl.DIM_ID='||g_object_id||' and lvl.LEVEL_TABLE_NAME=ltc.name';
88 if g_debug then
89 write_to_log_file_n(l_stmt||get_time);
90 end if;
91 execute immediate l_stmt;
92 if g_debug then
93 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
94 end if;
95 analyze_table_stats(substr(g_level_table,instr(g_level_table,'.')+1,
96 length(g_level_table)),substr(g_level_table,1,instr(g_level_table,'.')-1));
97 if g_err_rec_flag then
98 l_table:=g_dang_table||'1';
99 else
100 l_table:=g_dang_table;
101 end if;
102 if drop_table(l_table)=false then
103 null;
104 end if;
105 if g_number_pk_cols>0 then
106 l_stmt:='create table '||l_table||' tablespace '||g_src_op_table_space;
107 l_stmt:=l_stmt||' as select * from '||g_wh_dang_table||g_db_link_stmt||
108 ' where 1=2';
109 if g_debug then
110 write_to_log_file_n(l_stmt||get_time);
111 end if;
112 execute immediate l_stmt;
113 if g_debug then
114 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
115 end if;
116 l_number_db_columns:=0;
117 if get_db_columns_for_table(substr(l_table,instr(l_table,'.')+1),
118 l_db_columns,l_number_db_columns,g_src_bis_owner)=false then
119 null;
120 end if;
121 for i in 1..g_number_pk_cols loop
122 if value_in_table(l_db_columns,l_number_db_columns,g_pk_cols(i))=false then
123 if g_debug then
124 write_to_log_file_n('The remote table '||g_wh_dang_table||g_db_link_stmt||' does not have column ');
125 write_to_log_file(g_pk_cols(i)||'. So using value and PK. Cannot use columns listed in profile options');
126 end if;
127 g_number_pk_cols:=0;
128 g_number_profile_options:=0;
129 exit;
130 end if;
131 end loop;
132 l_number_db_columns:=0;
133 if drop_table(l_table)=false then
134 null;
135 end if;
136 end if;
137 l_stmt:='create table '||l_table||' tablespace '||g_src_op_table_space;
138 if g_src_parallel is not null then
139 l_stmt:=l_stmt||' parallel(degree '||g_src_parallel||') ';
140 end if;
141 l_stmt:=l_stmt||' as select distinct level_table,value';
142 for i in 1..g_number_pk_cols loop
143 l_stmt:=l_stmt||','||g_pk_cols(i);
144 end loop;
145 l_stmt:=l_stmt||' from '||g_wh_dang_table||g_db_link_stmt;
146 if g_debug then
147 write_to_log_file_n(l_stmt||get_time);
148 end if;
149 execute immediate l_stmt;
150 g_dang_table_count:=sql%rowcount;
151 if g_debug then
152 write_to_log_file_n('Created with '||g_dang_table_count||' rows '||get_time);
153 end if;
154 --truncate the remote table
155 if g_debug then
156 write_to_log_file_n('Going to truncate table '||g_wh_dang_table||' at '||g_db_link_stmt);
157 end if;
158 l_stmt:='begin EDW_OWB_COLLECTION_UTIL.truncate_table'||g_db_link_stmt||'('''||g_wh_dang_table||''');end;';
159 if g_debug then
160 write_to_log_file_n(l_stmt||get_time);
161 end if;
162 execute immediate l_stmt;
163 commit;
164 --if src is different from the warehouse, look at the source also for g_wh_dang_table on the src.
165 if g_src_same_wh_flag=false then
166 if does_table_have_data(g_wh_dang_table)=2 then
167 l_table2:=g_dang_table||'2';
168 l_table3:=g_dang_table||'3';
169 if drop_table(l_table2)=false then
170 null;
171 end if;
172 if drop_table(l_table3)=false then
173 null;
174 end if;
175 l_stmt:='create table '||l_table2||' tablespace '||g_src_op_table_space;
176 if g_src_parallel is not null then
177 l_stmt:=l_stmt||' parallel(degree '||g_src_parallel||') ';
178 end if;
179 l_stmt:=l_stmt||' as select distinct level_table,value';
180 for i in 1..g_number_pk_cols loop
181 l_stmt:=l_stmt||','||g_pk_cols(i);
182 end loop;
183 l_stmt:=l_stmt||' from '||g_wh_dang_table;
184 if g_debug then
185 write_to_log_file_n(l_stmt||get_time);
186 end if;
187 execute immediate l_stmt;
188 if g_debug then
189 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
190 end if;
191 l_stmt:='create table '||l_table3||' tablespace '||g_src_op_table_space;
192 if g_src_parallel is not null then
193 l_stmt:=l_stmt||' parallel(degree '||g_src_parallel||') ';
194 end if;
195 l_stmt:=l_stmt||' as select * from '||l_table;
196 if g_debug then
197 write_to_log_file_n(l_stmt||get_time);
198 end if;
199 execute immediate l_stmt;
200 if g_debug then
201 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
202 end if;
203 if drop_table(l_table)=false then
204 null;
205 end if;
206 l_stmt:='create table '||l_table||' tablespace '||g_src_op_table_space;
207 if g_src_parallel is not null then
208 l_stmt:=l_stmt||' parallel(degree '||g_src_parallel||') ';
209 end if;
210 l_stmt:=l_stmt||' as select level_table,value';
211 for i in 1..g_number_pk_cols loop
212 l_stmt:=l_stmt||','||g_pk_cols(i);
213 end loop;
214 l_stmt:=l_stmt||' from '||l_table2||' UNION select level_table,value';
215 for i in 1..g_number_pk_cols loop
216 l_stmt:=l_stmt||','||g_pk_cols(i);
217 end loop;
218 l_stmt:=l_stmt||' from '||l_table3;
219 if g_debug then
220 write_to_log_file_n(l_stmt||get_time);
221 end if;
222 execute immediate l_stmt;
223 g_dang_table_count:=sql%rowcount;
224 if g_debug then
225 write_to_log_file_n('Created with '||g_dang_table_count||' rows '||get_time);
226 end if;
227 if drop_table(l_table2)=false then
228 null;
229 end if;
230 if drop_table(l_table3)=false then
231 null;
232 end if;
233 end if;
234 end if;
235 if get_db_columns_for_table(substr(l_table,instr(l_table,'.')+1),
236 g_wh_dang_table_cols,g_number_wh_dang_table_cols,g_src_bis_owner)=false then
237 return false;
238 end if;
239 if g_debug then
240 write_to_log_file_n('DB columns for '||l_table);
241 for i in 1..g_number_wh_dang_table_cols loop
242 write_to_log_file(g_wh_dang_table_cols(i));
243 end loop;
244 end if;
245 if g_err_rec_flag then
246 l_number_db_columns:=0;
247 l_found:=true;
248 if get_db_columns_for_table(substr(g_dang_table,instr(g_dang_table,'.')+1),
249 l_db_columns,l_number_db_columns,g_src_bis_owner)=false then
250 if get_db_columns_for_table(substr(g_dang_table,instr(g_dang_table,'.')+1),
251 l_db_columns,l_number_db_columns,null)=false then
252 return false;
253 end if;
254 end if;
255 for i in 1..g_number_wh_dang_table_cols loop
256 if value_in_table(l_db_columns,l_number_db_columns,g_wh_dang_table_cols(i))=false then
257 if g_debug then
258 write_to_log_file_n('Column '||g_wh_dang_table_cols(i)||' not found in dang table '||g_dang_table);
259 end if;
260 l_found:=false;
261 exit;
262 end if;
263 end loop;
264 if l_found=false then
265 --a very rare occurance. if someone changed the pk structure in the middle of an error recovery
266 if drop_table(g_dang_table)=false then
267 null;
268 end if;
269 l_stmt:='create table '||g_dang_table||' tablespace '||g_src_op_table_space;
270 if g_src_parallel is not null then
271 l_stmt:=l_stmt||' parallel(degree '||g_src_parallel||') ';
272 end if;
273 l_stmt:=l_stmt||' as select * from '||l_table;
274 if g_debug then
275 write_to_log_file_n(l_stmt||get_time);
276 end if;
277 execute immediate l_stmt;
278 if g_debug then
279 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
280 end if;
281 g_err_rec_flag:=false;
282 else
283 l_table2:=g_dang_table||'2';
284 l_table3:=g_dang_table||'3';
285 l_stmt:='create table '||l_table2||' tablespace '||g_src_op_table_space;
286 if g_src_parallel is not null then
287 l_stmt:=l_stmt||' parallel(degree '||g_src_parallel||') ';
288 end if;
289 l_stmt:=l_stmt||' as select A.rowid row_id from '||l_table||' A,'||g_dang_table||' B '||
290 ' where ';
291 for i in 1..g_number_wh_dang_table_cols loop
292 if g_wh_dang_table_cols(i)<>'LEVEL_TABLE' then
293 l_stmt:=l_stmt||'nvl(A.'||g_wh_dang_table_cols(i)||',''null'')='||
294 'nvl(B.'||g_wh_dang_table_cols(i)||',''null'') and ';
295 else
296 l_stmt:=l_stmt||'A.'||g_wh_dang_table_cols(i)||'='||
297 'B.'||g_wh_dang_table_cols(i)||' and ';
298 end if;
299 end loop;
300 l_stmt:=substr(l_stmt,1,length(l_stmt)-4);
301 if drop_table(l_table2)=false then
302 null;
303 end if;
304 if g_debug then
305 write_to_log_file_n(l_stmt||get_time);
306 end if;
307 execute immediate l_stmt;
308 if g_debug then
309 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
310 end if;
311 l_stmt:='create table '||l_table3||' tablespace '||g_src_op_table_space;
312 if g_src_parallel is not null then
313 l_stmt:=l_stmt||' parallel(degree '||g_src_parallel||') ';
314 end if;
315 l_stmt:=l_stmt||' as select rowid row_id from '||l_table||' MINUS select row_id from '||l_table2;
316 if drop_table(l_table3)=false then
317 null;
318 end if;
319 if g_debug then
320 write_to_log_file_n(l_stmt||get_time);
321 end if;
322 execute immediate l_stmt;
323 if g_debug then
324 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
325 end if;
326 l_stmt:='insert into '||g_dang_table||'(';
327 for i in 1..g_number_wh_dang_table_cols loop
328 l_stmt:=l_stmt||g_wh_dang_table_cols(i)||',';
329 end loop;
330 l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
331 l_stmt:=l_stmt||') select ';
332 for i in 1..g_number_wh_dang_table_cols loop
333 l_stmt:=l_stmt||'B.'||g_wh_dang_table_cols(i)||',';
334 end loop;
335 l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
336 l_stmt:=l_stmt||' from '||l_table3||' A, '||l_table||' B where A.row_id=B.rowid';
337 if g_debug then
341 if g_debug then
338 write_to_log_file_n(l_stmt||get_time);
339 end if;
340 execute immediate l_stmt;
342 write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);
343 end if;
344 commit;
345 end if;
346 if drop_table(l_table)=false then
347 null;
348 end if;
349 if drop_table(l_table2)=false then
350 null;
351 end if;
352 if drop_table(l_table3)=false then
353 null;
354 end if;
355 end if;
356 analyze_table_stats(substr(g_dang_table,instr(g_dang_table,'.')+1,
357 length(g_dang_table)),substr(g_dang_table,1,instr(g_dang_table,'.')-1));
358 return true;
359 Exception when others then
360 g_status_message:=sqlerrm;
361 write_to_log_file_n(g_status_message);
362 return false;
363 End;
364
365 function get_hl_keys_from_view return boolean is
366 l_stmt varchar2(20000);
367 l_stmt1 varchar2(20000);
368 TYPE CurTyp IS REF CURSOR;
369 cv CurTyp;
370 l_level varcharTableType;
371 l_level_id numberTableType;
372 l_level_prefix varcharTableType;
373 l_number_level number;
374 l_table varchar2(400);
375 l_table2 varchar2(400);
376 l_table3 varchar2(400);
377 l_table4 varchar2(400);
378 l_lowest_level varchar2(400);
379 l_lowest_level_id number;
380 l_lowest_level_prefix varchar2(400);
381 l_lowest_level_pk varchar2(400);
382 l_level_pk varchar2(200);
383 Begin
384 if g_debug then
385 write_to_log_file_n('In get_hl_keys_from_view'||get_time);
386 end if;
387 l_table:=g_dang_table||'1';
388 if check_table(g_pk_view)=false then
389 if g_debug then
390 write_to_log_file_n('PK View '||g_pk_view||' not found. Cannot find higher level keys');
391 end if;
392 return true;
393 end if;
394 if get_db_columns_for_table(g_pk_view,g_pk_view_cols,g_number_pk_view_cols)=false then
395 return false;
396 end if;
397 if g_debug then
398 write_to_log_file_n('The DB columns of the view '||g_pk_view);
399 for i in 1..g_number_pk_view_cols loop
400 write_to_log_file(g_pk_view_cols(i));
401 end loop;
402 end if;
403 l_stmt:='select name,id,prefix from '||g_level_table;
404 if g_debug then
405 write_to_log_file_n(l_stmt||get_time);
406 end if;
407 l_number_level:=1;
408 open cv for l_stmt;
409 loop
410 fetch cv into l_level(l_number_level),l_level_id(l_number_level),l_level_prefix(l_number_level);
411 exit when cv%notfound;
412 l_number_level:=l_number_level+1;
413 end loop;
414 l_number_level:=l_number_level-1;
415 close cv;
416 if g_debug then
417 write_to_log_file_n('Level and prefix');
418 for i in 1..l_number_level loop
419 write_to_log_file(l_level(i)||' ('||l_level_id(i)||') ('||l_level_prefix(i)||')');
420 end loop;
421 end if;
422 l_stmt:='select EDW_OWB_COLLECTION_UTIL.get_lowest_level_table'||g_db_link_stmt||'(null,'||g_object_id||
423 ') from dual';
424 if g_debug then
425 write_to_log_file_n(l_stmt||get_time);
426 end if;
427 open cv for l_stmt;
428 fetch cv into l_lowest_level;
429 close cv;
430 for i in 1..l_number_level loop
431 if l_level(i)=l_lowest_level then
432 l_lowest_level_prefix:=l_level_prefix(i);
433 l_lowest_level_id:=l_level_id(i);
434 exit;
435 end if;
436 end loop;
437 if g_debug then
438 write_to_log_file_n('Lowest level '||l_lowest_level||'('||l_lowest_level_id||') '||l_lowest_level_prefix);
439 end if;
440 --l_lowest_level_pk
441 l_stmt:='select EDW_OWB_COLLECTION_UTIL.get_dim_pk'||g_db_link_stmt||'(null,'||g_object_id||
442 ') from dual';
443 if g_debug then
444 write_to_log_file_n(l_stmt||get_time);
445 end if;
446 open cv for l_stmt;
447 fetch cv into l_lowest_level_pk;
448 close cv;
449 l_lowest_level_pk:=substr(l_lowest_level_pk,1,instr(l_lowest_level_pk,'_KEY',-1)-1);
450 if g_debug then
451 write_to_log_file_n('Lowest level PK '||l_lowest_level_pk);
452 end if;
453 l_stmt:='create table '||l_table||' tablespace '||g_src_op_table_space;
454 if g_src_parallel is not null then
455 l_stmt:=l_stmt||' parallel(degree '||g_src_parallel||') ';
456 end if;
457 l_stmt:=l_stmt||' as select ';
458 for i in 1..g_number_pk_view_cols loop
459 if g_pk_view_cols(i)<>l_lowest_level_pk and value_in_table(g_pk_cols,g_number_pk_cols,
460 g_pk_view_cols(i))=false then
461 l_stmt:=l_stmt||'B.'||g_pk_view_cols(i)||',';
462 end if;
463 end loop;
464 l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
465 l_stmt:=l_stmt||' from '||g_pk_view||' B where 1=2';
466 if drop_table(l_table)=false then
467 null;
468 end if;
469 if g_debug then
470 write_to_log_file_n(l_stmt||get_time);
471 end if;
472 execute immediate l_stmt;
473 if g_debug then
474 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
475 end if;
476 l_stmt1:='insert ';
477 if g_src_parallel is not null then
478 l_stmt1:=l_stmt1||'/*+parallel(AA,'||g_src_parallel||')*/ ';
479 end if;
483 g_pk_view_cols(i))=false then
480 l_stmt1:=l_stmt1||' into '||l_table||' AA (';
481 for i in 1..g_number_pk_view_cols loop
482 if g_pk_view_cols(i)<>l_lowest_level_pk and value_in_table(g_pk_cols,g_number_pk_cols,
484 l_stmt1:=l_stmt1||g_pk_view_cols(i)||',';
485 end if;
486 end loop;
487 l_stmt1:=substr(l_stmt1,1,length(l_stmt1)-1);
488 l_stmt1:=l_stmt1||') select ';
489 for i in 1..g_number_pk_view_cols loop
490 if g_pk_view_cols(i)<>l_lowest_level_pk and value_in_table(g_pk_cols,g_number_pk_cols,
491 g_pk_view_cols(i))=false then
492 l_stmt1:=l_stmt1||'B.'||g_pk_view_cols(i)||',';
493 end if;
494 end loop;
495 l_stmt1:=substr(l_stmt1,1,length(l_stmt1)-1);
496 l_stmt1:=l_stmt1||' from '||g_dang_table||' A,'||g_pk_view||' B where ';
497 if g_number_profile_options>0 then
498 for k in 1..g_number_profile_options loop
499 l_stmt:=l_stmt1;
500 for i in 1..g_number_pk_cols loop
501 if g_pk_porfile_number(i)=k then
502 l_stmt:=l_stmt||' B.'||g_pk_cols(i)||'=A.'||g_pk_cols(i)||' and ';
503 end if;
504 end loop;
505 l_stmt:=substr(l_stmt,1,length(l_stmt)-4);
506 if g_err_rec_flag then
507 l_stmt:=l_stmt||' and A.level_table='||l_lowest_level_id;
508 end if;
509 if g_debug then
510 write_to_log_file_n(l_stmt||get_time);
511 end if;
512 execute immediate l_stmt;
513 if g_debug then
514 write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);
515 end if;
516 commit;
517 end loop;
518 else
519 l_stmt:=l_stmt1;
520 l_stmt:=l_stmt||' A.value=B.'||l_lowest_level_pk;
521 if g_err_rec_flag then
522 l_stmt:=l_stmt||' and A.level_table='||l_lowest_level_id;
523 end if;
524 if g_debug then
525 write_to_log_file_n(l_stmt||get_time);
526 end if;
527 execute immediate l_stmt;
528 if g_debug then
529 write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);
530 end if;
531 commit;
532 end if;
533 --insert into g_wh_dang_table
534 for i in 1..l_number_level loop
535 if l_level(i)<>l_lowest_level then
536 l_level_pk:=get_pk_for_level(l_level_prefix(i));
537 if l_level_pk is not null then
538 l_table2:=g_dang_table||'2';
539 l_stmt:='create table '||l_table2||' tablespace '||g_src_op_table_space;
540 if g_src_parallel is not null then
541 l_stmt:=l_stmt||' parallel(degree '||g_src_parallel||') ';
542 end if;
543 l_stmt:=l_stmt||' as select distinct '||l_level_pk||' from '||l_table;
544 if drop_table(l_table2)=false then
545 null;
546 end if;
547 if g_debug then
548 write_to_log_file_n(l_stmt||get_time);
549 end if;
550 execute immediate l_stmt;
551 if g_debug then
552 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
553 end if;
554 if g_err_rec_flag then
555 l_table3:=g_dang_table||'3';
556 l_stmt:='create table '||l_table3||' tablespace '||g_src_op_table_space;
557 if g_src_parallel is not null then
558 l_stmt:=l_stmt||' parallel(degree '||g_src_parallel||') ';
559 end if;
560 l_stmt:=l_stmt||' as select value '||l_level_pk||' from '||g_dang_table||
561 ' where level_table='||l_level_id(i);
562 if drop_table(l_table3)=false then
563 null;
564 end if;
565 if g_debug then
566 write_to_log_file_n(l_stmt||get_time);
567 end if;
568 execute immediate l_stmt;
569 if g_debug then
570 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
571 end if;
572 l_table4:=g_dang_table||'4';
573 l_stmt:='create table '||l_table4||' tablespace '||g_src_op_table_space;
574 if g_src_parallel is not null then
575 l_stmt:=l_stmt||' parallel(degree '||g_src_parallel||') ';
576 end if;
577 l_stmt:=l_stmt||' as select '||l_level_pk||' from '||l_table2||' MINUS select '||
578 l_level_pk||' from '||l_table3;
579 if drop_table(l_table4)=false then
580 null;
581 end if;
582 if g_debug then
583 write_to_log_file_n(l_stmt||get_time);
584 end if;
585 execute immediate l_stmt;
586 if g_debug then
587 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
588 end if;
589 if drop_table(l_table2)=false then
590 null;
591 end if;
592 if drop_table(l_table3)=false then
593 null;
594 end if;
595 else
596 l_table4:=l_table2;
597 end if;
598 l_stmt:='insert into '||g_dang_table||'(level_table,value) select '||l_level_id(i)||','||l_level_pk||
599 ' from '||l_table4;
600 if g_debug then
601 write_to_log_file_n(l_stmt||get_time);
602 end if;
603 execute immediate l_stmt;
604 if g_debug then
605 write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);
606 end if;
607 commit;
608 if drop_table(l_table4)=false then
609 null;
610 end if;
611 else
612 if g_debug then
613 write_to_log_file_n('No pk found for level '||l_level(i)||' in PK view ');
614 end if;
615 end if;
616 end if;
617 end loop;
618 if drop_table(l_table)=false then
619 null;
620 end if;
621 return true;
622 Exception when others then
623 g_status_message:=sqlerrm;
624 write_to_log_file_n(g_status_message);
625 return false;
626 End;
627
628 function init_all return boolean is
629 Begin
630 g_status_message:=null;
631 g_read_cfig_options:=false;
632 g_src_bis_owner:=get_db_user('BIS');
633 g_instance:=get_this_instance;
634 if g_instance is null then
635 write_to_log_file_n('No local instance found');
636 return false;
637 end if;
638 g_dang_table:=g_src_bis_owner||'.SADR_'||g_object_id||'_'||g_instance;
639 g_wh_dang_table:='EDW_ADR_'||g_object_id||'_'||g_instance;--view name
640 g_level_table:=g_src_bis_owner||'.SLID_'||g_object_id||'_'||g_instance;
641 g_number_pk_view_cols:=0;
642 g_number_wh_dang_table_cols:=0;
643 g_err_rec_flag:=false;
644 if g_read_cfig_options then
645 if read_cfig_options=false then
646 return false;
647 end if;
648 else
649 if read_profile_options=false then
650 return false;
651 end if;
652 end if;
653 g_debug:=true;
654 if g_debug then
655 write_to_log_file_n('Check for table '||g_dang_table||' for error recovery');
656 end if;
657 if does_table_have_data(g_dang_table)=2 then
658 g_err_rec_flag:=true;
659 end if;
660 if g_auto_dang_flag=false then
661 write_to_log_file_n('Auto Dangling Recovery NOT Implemented');
662 return true;
663 end if;
664 if get_pk_structure=false then
665 return false;
666 end if;
667 write_to_log_file_n('The option values');
668 write_to_log_file('g_src_bis_owner='||g_src_bis_owner);
669 write_to_log_file('g_instance='||g_instance);
670 write_to_log_file('g_src_op_table_space='||g_src_op_table_space);
671 write_to_log_file('g_src_parallel='||g_src_parallel);
672 return true;
673 Exception when others then
674 g_status_message:=sqlerrm;
675 write_to_log_file_n(g_status_message);
676 return false;
677 End;
678
679 function get_dim_id(p_object_name varchar2) return number is
680 l_stmt varchar2(5000);
681 TYPE CurTyp IS REF CURSOR;
682 cv CurTyp;
683 l_object_id number;
684 Begin
685 l_stmt:='select DIM_ID from edw_dimensions_md_v'||g_db_link_stmt||' where DIM_NAME=:a';
686 open cv for l_stmt using p_object_name;
687 fetch cv into l_object_id;
688 close cv;
689 return l_object_id;
690 Exception when others then
691 g_status_message:=sqlerrm;
692 write_to_log_file_n(g_status_message);
693 return null;
694 End;
695
696 function get_db_user(p_product varchar2) return varchar2 is
697 l_stmt varchar2(5000);
698 TYPE CurTyp IS REF CURSOR;
699 cv CurTyp;
700 l_dummy1 varchar2(2000);
701 l_dummy2 varchar2(2000);
702 l_schema varchar2(400);
703 Begin
704 if FND_INSTALLATION.GET_APP_INFO(p_product,l_dummy1, l_dummy2,l_schema) = false then
705 write_to_log_file_n('FND_INSTALLATION.GET_APP_INFO returned with error');
706 return null;
707 end if;
708 return l_schema;
709 Exception when others then
710 g_status_message:=sqlerrm;
711 write_to_log_file_n(g_status_message);
712 return null;
713 End;
714
715 function get_this_instance return varchar2 is
716 l_stmt varchar2(5000);
717 TYPE CurTyp IS REF CURSOR;
718 cv CurTyp;
719 l_instance varchar2(400);
720 Begin
721 l_stmt:='select instance_code from edw_local_instance';
722 open cv for l_stmt;
723 fetch cv into l_instance;
724 close cv;
725 return l_instance;
726 Exception when others then
727 g_status_message:=sqlerrm;
728 write_to_log_file_n(g_status_message);
729 return null;
730 End;
731
732 function get_default_tablespace return varchar2 is
733 TYPE CurTyp IS REF CURSOR;
734 cv CurTyp;
735 l_stmt varchar2(4000);
736 l_op_table_space varchar2(400);
737 Begin
738 l_stmt:='select default_tablespace from dba_users where username=:a';
739 if g_debug then
740 write_to_log_file_n('Going to execute '||l_stmt||' using '||g_src_bis_owner);
741 end if;
742 open cv for l_stmt using g_src_bis_owner;
743 fetch cv into l_op_table_space;
744 close cv;
745 return l_op_table_space;
746 Exception when others then
747 g_status_message:=sqlerrm;
748 write_to_log_file_n(g_status_message);
749 return null;
750 End;
751
752 function read_profile_options return boolean is
753 l_value varchar2(400);
754 TYPE CurTyp IS REF CURSOR;
755 cv CurTyp;
756 l_stmt varchar2(4000);
757 l_res number;
758 check_tspace_exist varchar(1);
759 check_ts_mode varchar(1);
760 physical_tspace_name varchar2(100);
761 Begin
762 l_value:=fnd_profile.value('EDW_DEBUG');
763 g_debug:=false;
764 if l_value='Y' then
765 g_debug:=true;
766 end if;
767 if g_debug then
768 write_to_log_file_n('Debug turned ON');
769 else
770 write_to_log_file_n('Debug turned OFF');
771 end if;
772 g_auto_dang_flag:=true;
773 l_stmt:='select 1 from edw_attribute_sets_md_v'||g_db_link_stmt||' sis where '||
774 'sis.ATTRIBUTE_GROUP_NAME=''AUTO_DANGLING_RECOVERY'' '||
775 'and sis.ENTITY_ID=:b';
776 if g_debug then
777 write_to_log_file_n(l_stmt);
778 end if;
779 open cv for l_stmt using g_object_id;
780 fetch cv into l_res;
781 close cv;
782 if l_res=1 then
783 g_auto_dang_flag:=false;
784 end if;
785 if g_auto_dang_flag=false then
786 return true;
787 end if;
788
789 g_src_op_table_space:=fnd_profile.value('EDW_OP_TABLE_SPACE');
790
791 if g_src_op_table_space is null then
792 AD_TSPACE_UTIL.is_new_ts_mode (check_ts_mode);
793 If check_ts_mode ='Y' then
794 AD_TSPACE_UTIL.get_tablespace_name ('BIS', 'INTERFACE','Y',check_tspace_exist, physical_tspace_name);
795 if check_tspace_exist='Y' and physical_tspace_name is not null then
796 g_src_op_table_space := physical_tspace_name;
797 end if;
798 end if;
799 end if;
800 if g_src_op_table_space is null then
801 g_src_op_table_space:=get_default_tablespace;
802 end if;
803
804 g_src_parallel:=fnd_profile.value('EDW_PARALLEL');
805 return true;
806 Exception when others then
807 g_status_message:=sqlerrm;
808 write_to_log_file_n(g_status_message);
809 return false;
810 End;
811
812 function read_cfig_options return boolean is
813 l_option_value varchar2(20);
814 Begin
815 l_option_value:=get_src_option('DEBUG');
816 if l_option_value='Y' then
817 write_to_log_file_n('Debug turned ON');
818 g_debug:=true;
819 else
820 write_to_log_file_n('Debug turned OFF');
821 g_debug:=false;
822 end if;
823 l_option_value:=null;
824 l_option_value:=get_src_option('AUTODANG');
825 if l_option_value='N' then
826 g_auto_dang_flag:=false;
827 else
828 g_auto_dang_flag:=true;
829 end if;
830 if g_auto_dang_flag=false then
831 return true;
832 end if;
833 l_option_value:=null;
834 l_option_value:=get_src_option('PARALLELISM');
835 if l_option_value is not null then
836 g_src_parallel:=to_number(l_option_value);
837 if g_src_parallel=0 then
838 g_src_parallel:=null;
839 end if;
840 else
841 g_src_parallel:=null;
842 end if;
843 if g_src_parallel is not null then
844 execute immediate 'alter session enable parallel dml';
845 end if;
846 g_src_op_table_space:=get_src_option('OPTABLESPACE');
847 if g_src_op_table_space is null then
848 g_src_op_table_space:=get_default_tablespace;
849 end if;
850 return true;
851 Exception when others then
852 g_status_message:=sqlerrm;
853 write_to_log_file_n(g_status_message);
854 return false;
855 End;
856
857 function get_src_option(p_option_code varchar2) return varchar2 is
858 TYPE CurTyp IS REF CURSOR;
859 cv CurTyp;
860 l_stmt varchar2(4000);
861 l_option_value varchar2(400);
862 Begin
863 l_stmt:='select EDW_OPTION.get_source_option'||g_db_link_stmt||'(null,'||g_object_id||','''||
864 p_option_code||''','''||g_instance||''') from dual';
865 if g_debug then
866 write_to_log_file_n(l_stmt||get_time);
867 end if;
868 open cv for l_stmt;
869 fetch cv into l_option_value;
870 close cv;
874 write_to_log_file_n(g_status_message);
871 return l_option_value;
872 Exception when others then
873 g_status_message:=sqlerrm;
875 return null;
876 End;
877
878
879 function get_time return varchar2 is
880 begin
881 return ' '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');
882 End;
883
884
885 procedure write_to_log_file(p_message varchar2) is
886 l_len number;
887 l_start number:=1;
888 l_end number:=1;
889 last_reached boolean:=false;
890 begin
891 if p_message is null or p_message='' then
892 return;
893 end if;
894 l_len:=nvl(length(p_message),0);
895 if l_len <=0 then
896 return;
897 end if;
898 fnd_file.new_line(FND_FILE.LOG,1);
899 while true loop
900 l_end:=l_start+250;
901 if l_end >= l_len then
902 l_end:=l_len;
903 last_reached:=true;
904 end if;
905 FND_FILE.PUT(FND_FILE.LOG,substr(p_message,l_start,250));
906 l_start:=l_start+250;
907 if last_reached then
908 exit;
909 end if;
910 end loop;
911 Exception when others then
912 null;
913 End;
914
915 procedure write_to_log_file_n(p_message varchar2) is
916 begin
917 write_to_log_file(' ');
918 write_to_log_file(p_message);
919 Exception when others then
920 g_status_message:=sqlerrm;
921 null;
922 end;
923
924 function drop_table(p_table_name varchar2,p_owner varchar2) return boolean is
925 l_stmt varchar2(4000);
926 Begin
927 if g_debug then
928 write_to_log_file_n('In drop_table '||p_table_name||' owner '||p_owner);
929 end if;
930 if p_owner is null then
931 l_stmt:='drop table '||p_table_name;
932 if g_debug then
933 write_to_log_file_n('Going to execute '||l_stmt);
934 end if;
935 execute immediate l_stmt;
936 else
937 l_stmt:='drop table '||p_owner||'.'||p_table_name;
938 if g_debug then
939 write_to_log_file_n('Going to execute '||l_stmt);
940 end if;
941 execute immediate l_stmt;
942 end if;
943 return true;
944 Exception when others then
945 write_to_log_file_n('Could not drop table '||sqlerrm);
946 return false;
947 End;
948
949 function check_table(p_table varchar2, p_owner varchar2) return boolean is
950 l_stmt varchar2(10000);
951 TYPE CurTyp IS REF CURSOR;
952 cv CurTyp;
953 Begin
954 if g_debug then
955 write_to_log_file_n('In check_table for '||p_table||' and owner '||p_owner);
956 end if;
957 begin
958 if p_owner is null then
959 l_stmt:='select 1 from '||p_table||' where rownum=1';
960 else
961 l_stmt:='select 1 from '||p_owner||'.'||p_table||' where rownum=1';
962 end if;
963 open cv for l_stmt;
964 close cv;
965 if g_debug then
966 write_to_log_file('Table found');
967 end if;
968 return true;
969 exception when others then
970 g_status_message:=sqlerrm;
971 if g_debug then
972 write_to_log_file('Table NOT found');
973 end if;
974 return false;
975 end;
976 Exception when others then
977 g_status_message:=sqlerrm;
978 write_to_log_file_n('Exception in check_table '||sqlerrm||' '||get_time);
979 return false;
980 End;
981
982 function get_db_columns_for_table(
983 p_table varchar2,
984 p_columns OUT NOCOPY varcharTableType,
985 p_number_columns OUT NOCOPY number,
986 p_owner varchar2) return boolean is
987 l_stmt varchar2(5000);
988 TYPE CurTyp IS REF CURSOR;
989 cv CurTyp;
990 Begin
991 if g_debug then
992 write_to_log_file_n('In get_db_columns_for_table, Input params is '||p_table||
993 ' and owner '||p_owner);
994 end if;
995 p_number_columns:=1;
996
997 if p_owner is not null then
998 l_stmt:='select column_name from all_tab_columns where table_name=:a and owner=:b order by column_id';
999 if g_debug then
1000 write_to_log_file_n('Going to execute '||l_stmt);
1001 end if;
1002 open cv for l_stmt using p_table,p_owner;
1003 else
1004 l_stmt:='select tab.column_name from all_tab_columns tab, user_synonyms syn where
1005 tab.table_name=:a and tab.table_name=syn.table_name and tab.owner=syn.table_owner order by tab.column_id';
1006
1007 if g_debug then
1008 write_to_log_file_n('Going to execute '||l_stmt);
1009 end if;
1010 open cv for l_stmt using p_table;
1011 end if;
1012
1013 loop
1014 fetch cv into p_columns(p_number_columns);
1015 exit when cv%notfound;
1016 p_number_columns:=p_number_columns+1;
1017 end loop;
1018 close cv;
1019 p_number_columns:=p_number_columns-1;
1020 return true;
1021 Exception when others then
1022 g_status_message:=sqlerrm;
1023 write_to_log_file_n(g_status_message);
1024 p_number_columns:=0;
1025 return false;
1026 End;
1027
1028 procedure analyze_table_stats(p_table varchar2, p_owner varchar2) is
1029 errbuf varchar2(2000);
1030 retcode varchar2(200);
1031 l_owner varchar2(400);
1032 Begin
1033 if g_debug then
1037 if g_src_parallel is null then
1034 write_to_log_file_n('In analyze_table_stats. table is '||p_table||' and p_owner is '||p_owner);
1035 end if;
1036 l_owner:=p_owner;
1038 FND_STATS.GATHER_TABLE_STATS (errbuf, retcode, l_owner, p_table,null,1);
1039 else
1040 FND_STATS.GATHER_TABLE_STATS (errbuf, retcode, l_owner, p_table,null,g_src_parallel);
1041 end if;
1042 if retcode <> '0' then
1043 write_to_log_file_n('FND_STATS.GATHER_TABLE_STATS status message is '||errbuf);
1044 end if;
1045 Exception when others then
1046 g_status_message:=sqlerrm;
1047 write_to_log_file_n('Error in analyze_table_stats '||sqlerrm);
1048 End;
1049
1050 function get_pk_structure return boolean is
1051 l_start number;
1052 l_end number;
1053 l_length number;
1054 l_col varchar2(200);
1055 l_pk_structure varchar2(800);
1056 l_stmt varchar2(2000);
1057 TYPE CurTyp IS REF CURSOR;
1058 cv CurTyp;
1059 Begin
1060 l_stmt:='select profile_option_name from fnd_profile_options where profile_option_name '||
1061 ' like '''||g_object_name||'_PS%''';
1062 if g_debug then
1063 write_to_log_file_n(l_stmt);
1064 end if;
1065 g_number_profile_options:=1;
1066 open cv for l_stmt;
1067 loop
1068 fetch cv into g_profile_options(g_number_profile_options);
1069 exit when cv%notfound;
1070 g_number_profile_options:=g_number_profile_options+1;
1071 end loop;
1072 g_number_profile_options:=g_number_profile_options-1;
1073 g_number_pk_cols:=0;
1074 for i in 1..g_number_profile_options loop
1075 if g_debug then
1076 write_to_log_file_n('Looking at '||g_profile_options(i));
1077 end if;
1078 l_pk_structure:=null;
1079 l_pk_structure:=fnd_profile.value(g_profile_options(i));
1080 if l_pk_structure is not null then
1081 l_start:=1;
1082 l_end:=1;
1083 l_length:=length(l_pk_structure);
1084 loop
1085 l_end:=instr(l_pk_structure,'-',l_start);
1086 if l_end=0 then
1087 l_end:=l_length+1;
1088 end if;
1089 l_col:=substr(l_pk_structure,l_start,(l_end-l_start));
1090 if l_col<>'INST' then
1091 g_number_pk_cols:=g_number_pk_cols+1;
1092 g_pk_cols(g_number_pk_cols):=l_col;
1093 g_pk_porfile_number(g_number_pk_cols):=i;
1094 end if;
1095 if l_end>l_length then
1096 exit;
1097 end if;
1098 l_start:=l_end+1;
1099 end loop;
1100 end if;
1101 end loop;
1102 if g_debug then
1103 write_to_log_file_n('All the columns paresed from pk structure ');
1104 for i in 1..g_number_pk_cols loop
1105 write_to_log_file(g_pk_porfile_number(i)||' '||g_pk_cols(i));
1106 end loop;
1107 end if;
1108 return true;
1109 Exception when others then
1110 g_status_message:=sqlerrm;
1111 write_to_log_file_n('Error in get_pk_structure '||g_status_message);
1112 return false;
1113 End;
1114
1115 function value_in_table(
1116 p_table varcharTableType,
1117 l_number_table number,
1118 p_value varchar2) return boolean is
1119 Begin
1120 if p_value is null or l_number_table <=0 then
1121 return false;
1122 end if;
1123 for i in 1..l_number_table loop
1124 if p_table(i)=p_value then
1125 return true;
1126 end if;
1127 end loop;
1128 return false;
1129 Exception when others then
1130 g_status_message:=sqlerrm;
1131 write_to_log_file_n('Exception in value_in_table '||sqlerrm);
1132 return false;
1133 End;
1134
1135 function get_pk_for_level(p_level_prefix varchar2) return varchar2 is
1136 Begin
1137 for i in 1..g_number_pk_view_cols loop
1138 if instr(g_pk_view_cols(i),p_level_prefix||'_',1)=1 then
1139 if value_in_table(g_pk_cols,g_number_pk_cols,g_pk_view_cols(i))=false then
1140 return g_pk_view_cols(i);
1141 end if;
1142 end if;
1143 end loop;
1144 return null;
1145 Exception when others then
1146 g_status_message:=sqlerrm;
1147 write_to_log_file_n('Exception in get_pk_for_level '||sqlerrm);
1148 return null;
1149 End;
1150
1151 /*
1152 does_table_have_data :
1153 0 : Error
1154 1: no data
1155 2: data present
1156 */
1157 function does_table_have_data(p_table varchar2, p_where varchar2) return number is
1158 l_stmt varchar2(2000);
1159 TYPE CurTyp IS REF CURSOR;
1160 cv CurTyp;
1161 l_res number:=null;
1162 Begin
1163 if g_debug then
1164 write_to_log_file_n('In does_table_have_data , table is '||p_table||' and where clause is '||p_where);
1165 end if;
1166 if p_where is null then
1167 l_stmt:='select 1 from '||p_table||' where rownum=1';
1168 else
1169 l_stmt:='select 1 from '||p_table||' where '||p_where||' and rownum=1';
1170 end if;
1171 open cv for l_stmt;
1172 fetch cv into l_res;
1173 close cv;
1174 if l_res is null then
1175 if g_debug then
1176 write_to_log_file('No');
1177 end if;
1178 return 1;
1179 end if;
1180 if g_debug then
1181 write_to_log_file('Yes');
1182 end if;
1183 return 2;
1184 Exception when others then
1185 g_status_message:=sqlerrm;
1186 write_to_log_file_n('Error in does_table_have_data '||sqlerrm);
1187 return 0;
1188 End;
1189
1190 function create_missing_key_view return boolean is
1191 l_stmt varchar2(2000);
1192 Begin
1193 if g_debug then
1194 write_to_log_file_n('In create_missing_key_view'||get_time);
1195 end if;
1196 l_stmt:='create or replace view '||g_missing_key_view||' as select '''||g_object_name||''' dimension_name,'||
1197 'A.name level_table,B.value value';
1198 for i in 1..g_number_pk_cols loop
1199 l_stmt:=l_stmt||',B.'||g_pk_cols(i)||' '||g_pk_cols(i);
1200 end loop;
1201 l_stmt:=l_stmt||' from '||g_level_table||' A,'||g_dang_table||' B where A.id=B.level_table';
1202 if g_debug then
1203 write_to_log_file_n(l_stmt||get_time);
1204 end if;
1205 execute immediate l_stmt;
1206 if g_debug then
1207 write_to_log_file_n('Created view '||get_time);
1208 end if;
1209 return true;
1210 Exception when others then
1211 g_status_message:=sqlerrm;
1212 write_to_log_file_n('Exception in create_missing_key_view '||sqlerrm);
1213 return false;
1214 End;
1215
1216 --will be called from wrap_up in collection util
1217 procedure truncate_dang_table is
1218 Begin
1219 execute immediate 'truncate table '||g_dang_table;
1220 Exception when others then
1221 g_status_message:=sqlerrm;
1222 write_to_log_file_n('Exception in truncate_dang_table '||sqlerrm);
1223 End;
1224
1225 END EDW_SRC_DANG_RECOVERY;
[Home] [Help]