[Home] [Help]
5: procedure collect_dimension_main(
6: p_conc_id number,
7: p_conc_name varchar2,
8: p_dim_name varchar2,
9: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
10: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
11: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
12: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
13: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
6: p_conc_id number,
7: p_conc_name varchar2,
8: p_dim_name varchar2,
9: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
10: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
11: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
12: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
13: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
14: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
7: p_conc_name varchar2,
8: p_dim_name varchar2,
9: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
10: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
11: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
12: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
13: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
14: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
15: p_number_levels number,
8: p_dim_name varchar2,
9: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
10: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
11: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
12: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
13: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
14: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
15: p_number_levels number,
16: p_debug boolean,
9: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
10: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
11: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
12: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
13: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
14: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
15: p_number_levels number,
16: p_debug boolean,
17: p_exec_flag boolean,
10: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
11: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
12: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
13: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
14: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
15: p_number_levels number,
16: p_debug boolean,
17: p_exec_flag boolean,
18: p_bis_owner varchar2,
20: p_collection_size number,
21: p_table_owner varchar2,
22: p_forall_size number,
23: p_update_type varchar2,
24: p_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
25: p_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
26: p_number_skip_cols number,
27: p_load_pk number,
28: p_fresh_restart boolean,
21: p_table_owner varchar2,
22: p_forall_size number,
23: p_update_type varchar2,
24: p_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
25: p_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
26: p_number_skip_cols number,
27: p_load_pk number,
28: p_fresh_restart boolean,
29: p_op_table_space varchar2,
142: procedure collect_dimension_multi_thread(
143: p_conc_id number,
144: p_conc_name varchar2,
145: p_dim_name varchar2,
146: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
147: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
148: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
149: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
150: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
143: p_conc_id number,
144: p_conc_name varchar2,
145: p_dim_name varchar2,
146: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
147: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
148: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
149: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
150: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
151: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
144: p_conc_name varchar2,
145: p_dim_name varchar2,
146: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
147: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
148: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
149: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
150: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
151: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
152: p_number_levels number,
145: p_dim_name varchar2,
146: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
147: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
148: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
149: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
150: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
151: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
152: p_number_levels number,
153: p_debug boolean,
146: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
147: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
148: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
149: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
150: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
151: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
152: p_number_levels number,
153: p_debug boolean,
154: p_exec_flag boolean,
147: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
148: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
149: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
150: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
151: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
152: p_number_levels number,
153: p_debug boolean,
154: p_exec_flag boolean,
155: p_bis_owner varchar2,
157: p_collection_size number,
158: p_table_owner varchar2,
159: p_forall_size number,
160: p_update_type varchar2,
161: p_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
162: p_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
163: p_number_skip_cols number,
164: p_load_pk number,
165: p_fresh_restart boolean,
158: p_table_owner varchar2,
159: p_forall_size number,
160: p_update_type varchar2,
161: p_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
162: p_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
163: p_number_skip_cols number,
164: p_load_pk number,
165: p_fresh_restart boolean,
166: p_op_table_space varchar2,
185: p_dd_status_table varchar2
186: ) is
187: l_input_table varchar2(200);
188: l_ilog_table varchar2(100);
189: l_ok_low_end EDW_OWB_COLLECTION_UTIL.numberTableType;
190: l_ok_high_end EDW_OWB_COLLECTION_UTIL.numberTableType;
191: l_ok_end_count integer;
192: l_job_id EDW_OWB_COLLECTION_UTIL.numberTableType;
193: l_number_jobs number;
186: ) is
187: l_input_table varchar2(200);
188: l_ilog_table varchar2(100);
189: l_ok_low_end EDW_OWB_COLLECTION_UTIL.numberTableType;
190: l_ok_high_end EDW_OWB_COLLECTION_UTIL.numberTableType;
191: l_ok_end_count integer;
192: l_job_id EDW_OWB_COLLECTION_UTIL.numberTableType;
193: l_number_jobs number;
194: -----------------------------------------
188: l_ilog_table varchar2(100);
189: l_ok_low_end EDW_OWB_COLLECTION_UTIL.numberTableType;
190: l_ok_high_end EDW_OWB_COLLECTION_UTIL.numberTableType;
191: l_ok_end_count integer;
192: l_job_id EDW_OWB_COLLECTION_UTIL.numberTableType;
193: l_number_jobs number;
194: -----------------------------------------
195: l_temp_conc_name varchar2(200);
196: l_temp_conc_short_name varchar2(200);
255: else
256: write_to_log_file('Parallel drill down false');
257: end if;
258: end if;
259: g_dim_id:=EDW_OWB_COLLECTION_UTIL.get_object_id(g_dim_name);
260: if g_debug then
261: write_to_log_file_n('g_dim_id='||g_dim_id);
262: end if;
263: if g_dim_id=-1 then
260: if g_debug then
261: write_to_log_file_n('g_dim_id='||g_dim_id);
262: end if;
263: if g_dim_id=-1 then
264: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
265: g_status:=false;
266: return;
267: end if;
268: l_input_table:=p_bis_owner||'.INP_TAB_'||g_dim_id;
270: g_job_id:=null;
271: g_status:=true;
272: g_job_status_table:=p_job_status_table;
273: g_all_done:=false;
274: if EDW_OWB_COLLECTION_UTIL.create_dim_load_input_table(
275: p_dim_name,
276: l_input_table,
277: p_conc_id,
278: p_conc_name,
312: p_trace,
313: p_read_cfig_options,
314: p_max_fk_density
315: )=false then
316: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_status_message;
317: g_status:=false;
318: end if;
319: if g_status then
320: if initial_set_up(
330: if g_status then
331: if g_dim_direct_load then
332: insert_into_star;
333: else
334: if EDW_OWB_COLLECTION_UTIL.update_dim_load_input_table(
335: l_input_table,
336: l_ilog_table,
337: g_skip_ilog_update,
338: g_level_change,--to recreate from stmt etc
343: g_levels_I,
344: g_use_ltc_ilog,
345: g_number_levels
346: )=false then
347: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_status_message;
348: g_status:=false;
349: end if;
350: if g_status then
351: --BRING IN THE CALL TO COLLECT
350: if g_status then
351: --BRING IN THE CALL TO COLLECT
352: --if l_ilog_table is null, there are no records to load
353: if l_ilog_table is not null then
354: if EDW_OWB_COLLECTION_UTIL.find_ok_distribution(
355: l_ilog_table,
356: g_bis_owner,
357: p_max_threads,
358: p_min_job_load_size,
358: p_min_job_load_size,
359: l_ok_low_end,
360: l_ok_high_end,
361: l_ok_end_count)=false then
362: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_status_message;
363: g_status:=false;
364: end if;
365: --l_ok_end_count decides the number of threads
366: if g_status then
443: l_ok_high_end(j)
444: );
445: end if;
446: end loop;
447: if EDW_OWB_COLLECTION_UTIL.wait_on_jobs(
448: l_job_id,
449: l_number_jobs,
450: p_sleep_time,
451: g_thread_type)=false then
448: l_job_id,
449: l_number_jobs,
450: p_sleep_time,
451: g_thread_type)=false then
452: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_status_message;
453: g_status:=false;
454: g_status:=false;
455: end if;
456: if g_status then
453: g_status:=false;
454: g_status:=false;
455: end if;
456: if g_status then
457: if EDW_OWB_COLLECTION_UTIL.check_all_child_jobs(
458: p_job_status_table,
459: l_job_id,
460: l_number_jobs,
461: p_dim_name)=false then
465: end if;
466: --dont delete the conc pgm. we may need the conc request log file
467: /*if g_thread_type='CONC' then
468: --drop the conc programs
469: if EDW_OWB_COLLECTION_UTIL.delete_conc_program(
470: l_temp_conc_short_name,
471: l_temp_exe_name,
472: l_bis_short_name,
473: 'SHORT')=false then
483: if g_status then
484: if drop_input_tables(l_input_table)=false then
485: null;
486: end if;
487: if EDW_OWB_COLLECTION_UTIL.drop_table(l_ilog_table)=false then
488: null;
489: end if;
490: if EDW_OWB_COLLECTION_UTIL.drop_table(p_job_status_table)=false then
491: null;
486: end if;
487: if EDW_OWB_COLLECTION_UTIL.drop_table(l_ilog_table)=false then
488: null;
489: end if;
490: if EDW_OWB_COLLECTION_UTIL.drop_table(p_job_status_table)=false then
491: null;
492: end if;
493: if drop_prot_table(g_insert_prot_table,g_insert_prot_table_active)=false then
494: null;
522: g_before_update_table_final
523: */
524: g_max_threads:=p_max_threads;
525: g_debug:=p_debug;
526: if EDW_OWB_COLLECTION_UTIL.create_job_status_table(g_job_status_table,g_op_table_space)=false then
527: return false;
528: end if;
529: init_all(g_job_id);
530: /*
538: if drop_restart_tables=false then
539: return false;
540: end if;
541: else
542: if EDW_OWB_COLLECTION_UTIL.merge_all_ilog_tables(g_ilog,g_ilog,g_ilog||'A','IL',g_op_table_space,g_bis_owner,
543: g_parallel)=false then
544: return false;
545: end if;
546: end if;
557: return false;
558: end if;
559: if g_dim_empty_flag = false then
560: if check_snapshot_logs = false then
561: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
562: null;
563: end if;
564: insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,'RM'||g_job_id,'U');
565: write_to_log_file_n('No incremental data to be considered for any level ');
591: if recover_from_previous_error= false then
592: return false;
593: end if;
594: --get the fks in the map
595: if EDW_OWB_COLLECTION_UTIL.get_fks_in_dim(g_dim_name,g_src_fk_table,g_src_fk,g_number_src_fk_table)=false then
596: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
597: g_status:=false;
598: return false;
599: end if;
592: return false;
593: end if;
594: --get the fks in the map
595: if EDW_OWB_COLLECTION_UTIL.get_fks_in_dim(g_dim_name,g_src_fk_table,g_src_fk,g_number_src_fk_table)=false then
596: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
597: g_status:=false;
598: return false;
599: end if;
600: if g_debug then
657: return false;
658: end if;
659: end if;
660: insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,'CPTBL'||g_job_id,'U');
661: if edw_owb_collection_util.is_column_in_table(g_ilog,'row_num',g_bis_owner)=false then
662: if put_rownum_in_ok_table=false then
663: g_status:=false;
664: return false;
665: end if;
717: g_job_id:=p_job_id;
718: g_jobid_stmt:=' Job '||g_job_id||' ';
719: g_status:=true;
720: g_dim_name:=p_dim_name;
721: EDW_OWB_COLLECTION_UTIL.init_all(g_dim_name||'_'||g_job_id,null,'bis.edw.loader');
722: write_to_log_file_n('In COLLECT_DIMENSION. p_dim_name='||p_dim_name||', p_table_name='||p_table_name||
723: ', p_job_id='||p_job_id||', p_ok_low_end='||p_ok_low_end||', p_ok_high_end='||p_ok_high_end||get_time);
724: --g_job_id is not the dbms_job job_id. its just an identifier. goes from 1..n
725: /*need to open log file
725: /*need to open log file
726: this wont be conc manager log file
727: */
728: if COLLECT_DIMENSION(p_table_name,p_ok_low_end,p_ok_high_end)=false then
729: if EDW_OWB_COLLECTION_UTIL.log_into_job_status_table(
730: g_job_status_table,
731: g_dim_name,
732: g_job_id,
733: 'ERROR',
734: g_status_message)=false then
735: null;
736: end if;
737: else
738: if EDW_OWB_COLLECTION_UTIL.log_into_job_status_table(
739: g_job_status_table,
740: g_dim_name,
741: g_job_id,
742: 'SUCCESS',
759: if read_options_table(p_table_name)=false then
760: return false;
761: end if;
762: --g_conc_program_id is read from p_table_name
763: EDW_OWB_COLLECTION_UTIL.set_conc_program_id(g_conc_id);
764: EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
765: EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
766: EDW_OWB_COLLECTION_UTIL.set_g_read_cfig_options(g_read_cfig_options);
767: if set_session_parameters=false then
760: return false;
761: end if;
762: --g_conc_program_id is read from p_table_name
763: EDW_OWB_COLLECTION_UTIL.set_conc_program_id(g_conc_id);
764: EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
765: EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
766: EDW_OWB_COLLECTION_UTIL.set_g_read_cfig_options(g_read_cfig_options);
767: if set_session_parameters=false then
768: g_status:=false;
761: end if;
762: --g_conc_program_id is read from p_table_name
763: EDW_OWB_COLLECTION_UTIL.set_conc_program_id(g_conc_id);
764: EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
765: EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
766: EDW_OWB_COLLECTION_UTIL.set_g_read_cfig_options(g_read_cfig_options);
767: if set_session_parameters=false then
768: g_status:=false;
769: return false;
762: --g_conc_program_id is read from p_table_name
763: EDW_OWB_COLLECTION_UTIL.set_conc_program_id(g_conc_id);
764: EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
765: EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
766: EDW_OWB_COLLECTION_UTIL.set_g_read_cfig_options(g_read_cfig_options);
767: if set_session_parameters=false then
768: g_status:=false;
769: return false;
770: end if; --alter session etc
767: if set_session_parameters=false then
768: g_status:=false;
769: return false;
770: end if; --alter session etc
771: g_dim_id:=EDW_OWB_COLLECTION_UTIL.get_object_id(g_dim_name);
772: if g_dim_id=-1 then
773: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
774: return false;
775: end if;
769: return false;
770: end if; --alter session etc
771: g_dim_id:=EDW_OWB_COLLECTION_UTIL.get_object_id(g_dim_name);
772: if g_dim_id=-1 then
773: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
774: return false;
775: end if;
776: Init_all(g_job_id);--all tables will get job_id appended to them
777: /*
780: */
781: if read_options_table(p_table_name)=false then
782: return false;
783: end if;
784: EDW_OWB_COLLECTION_UTIL.set_up(g_dim_name);
785: --if set_level_I_flag=false then
786: --return false;
787: --end if;
788: if get_dim_storage=false then
804: get_dim_map_ids;
805: if g_status=false then
806: return false;
807: end if;
808: if EDW_OWB_COLLECTION_UTIL.get_fks_in_dim(g_dim_name,g_src_fk_table,g_src_fk,g_number_src_fk_table)=false then
809: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
810: g_status:=false;
811: return false;
812: end if;
805: if g_status=false then
806: return false;
807: end if;
808: if EDW_OWB_COLLECTION_UTIL.get_fks_in_dim(g_dim_name,g_src_fk_table,g_src_fk,g_number_src_fk_table)=false then
809: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
810: g_status:=false;
811: return false;
812: end if;
813: if g_debug then
876: procedure collect_dimension(
877: p_conc_id number,
878: p_conc_name varchar2,
879: p_dim_name varchar2,
880: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
881: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
882: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
883: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
884: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
877: p_conc_id number,
878: p_conc_name varchar2,
879: p_dim_name varchar2,
880: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
881: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
882: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
883: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
884: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
885: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
878: p_conc_name varchar2,
879: p_dim_name varchar2,
880: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
881: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
882: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
883: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
884: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
885: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
886: p_number_levels number,
879: p_dim_name varchar2,
880: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
881: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
882: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
883: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
884: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
885: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
886: p_number_levels number,
887: p_debug boolean,
880: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
881: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
882: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
883: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
884: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
885: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
886: p_number_levels number,
887: p_debug boolean,
888: p_exec_flag boolean,
881: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
882: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
883: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
884: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
885: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
886: p_number_levels number,
887: p_debug boolean,
888: p_exec_flag boolean,
889: p_bis_owner varchar2,
891: p_collection_size number,
892: p_table_owner varchar2,
893: p_forall_size number,
894: p_update_type varchar2,
895: p_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
896: p_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
897: p_number_skip_cols number,
898: p_load_pk number,
899: p_fresh_restart boolean,
892: p_table_owner varchar2,
893: p_forall_size number,
894: p_update_type varchar2,
895: p_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
896: p_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
897: p_number_skip_cols number,
898: p_load_pk number,
899: p_fresh_restart boolean,
900: p_op_table_space varchar2,
1006: end if;
1007: write_to_log_file('g_max_fk_density='||g_max_fk_density);
1008: write_to_log_file('g_analyze_freq='||g_analyze_freq);
1009: end if;
1010: g_dim_id:=EDW_OWB_COLLECTION_UTIL.get_object_id(g_dim_name);
1011: if g_dim_id=-1 then
1012: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
1013: return;
1014: end if;
1008: write_to_log_file('g_analyze_freq='||g_analyze_freq);
1009: end if;
1010: g_dim_id:=EDW_OWB_COLLECTION_UTIL.get_object_id(g_dim_name);
1011: if g_dim_id=-1 then
1012: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
1013: return;
1014: end if;
1015: init_all(g_job_id);
1016: /*
1024: if drop_restart_tables=false then
1025: return;
1026: end if;
1027: else
1028: if EDW_OWB_COLLECTION_UTIL.merge_all_ilog_tables(g_ilog,g_ilog,g_ilog||'A','IL',g_op_table_space,g_bis_owner,
1029: g_parallel)=false then
1030: g_status:=false;
1031: return;
1032: end if;
1044: return;
1045: end if;
1046: if g_dim_empty_flag = false then
1047: if check_snapshot_logs = false then
1048: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
1049: null;
1050: end if;
1051: insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,'RM'||g_job_id,'U');
1052: write_to_log_file_n('No incremental data to be considered for any level ');
1081: if recover_from_previous_error= false then
1082: return;
1083: end if;
1084: --get the fks in the map
1085: if EDW_OWB_COLLECTION_UTIL.get_fks_in_dim(g_dim_name,g_src_fk_table,g_src_fk,g_number_src_fk_table)=false then
1086: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
1087: g_status:=false;
1088: return;
1089: end if;
1082: return;
1083: end if;
1084: --get the fks in the map
1085: if EDW_OWB_COLLECTION_UTIL.get_fks_in_dim(g_dim_name,g_src_fk_table,g_src_fk,g_number_src_fk_table)=false then
1086: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
1087: g_status:=false;
1088: return;
1089: end if;
1090: if g_debug then
1264: g_skip_ilog_update:=false;
1265: l_status:=2;
1266: end if;
1267: else
1268: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_ilog,' status=1 ')=2 then
1269: l_status:=2;
1270: else
1271: l_status:=set_gilog_status;
1272: end if;
1397: if delete_gilog_status = false then --delete where status=1
1398: return;
1399: end if;
1400: end if;
1401: if EDW_OWB_COLLECTION_UTIL.record_coll_progress(g_dim_name, g_object_type,
1402: g_number_rows_processed,'PROCESSING','UPDATE')=false then
1403: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
1404: write_to_log_file_n(g_status_message);
1405: g_status:=false;
1399: end if;
1400: end if;
1401: if EDW_OWB_COLLECTION_UTIL.record_coll_progress(g_dim_name, g_object_type,
1402: g_number_rows_processed,'PROCESSING','UPDATE')=false then
1403: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
1404: write_to_log_file_n(g_status_message);
1405: g_status:=false;
1406: return;
1407: end if;
1408: commit;
1409: if g_debug then
1410: write_to_log_file_n('commit');
1411: end if;
1412: if EDW_OWB_COLLECTION_UTIL.truncate_table(g_dim_name_temp_int) = false then
1413: return;
1414: end if;
1415: if EDW_OWB_COLLECTION_UTIL.truncate_table(g_dim_name_temp) = false then
1416: return;
1411: end if;
1412: if EDW_OWB_COLLECTION_UTIL.truncate_table(g_dim_name_temp_int) = false then
1413: return;
1414: end if;
1415: if EDW_OWB_COLLECTION_UTIL.truncate_table(g_dim_name_temp) = false then
1416: return;
1417: end if;
1418: if g_update_star_flag then
1419: if EDW_OWB_COLLECTION_UTIL.truncate_table(g_dim_name_hold) = false then
1415: if EDW_OWB_COLLECTION_UTIL.truncate_table(g_dim_name_temp) = false then
1416: return;
1417: end if;
1418: if g_update_star_flag then
1419: if EDW_OWB_COLLECTION_UTIL.truncate_table(g_dim_name_hold) = false then
1420: return;
1421: end if;
1422: end if;
1423: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_with_slow)=false then
1419: if EDW_OWB_COLLECTION_UTIL.truncate_table(g_dim_name_hold) = false then
1420: return;
1421: end if;
1422: end if;
1423: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_with_slow)=false then
1424: null;
1425: end if;
1426: end if; --if there are still records in the ilog to consider
1427: end loop;
1687: l_stmt1:=l_stmt1||' as select rowid row_id from '||g_lowest_level;
1688: if g_debug then
1689: write_to_log_file_n('Going to execute '||l_stmt1||get_time);
1690: end if;
1691: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table)=false then
1692: null;
1693: end if;
1694: execute immediate l_stmt1;
1695: if g_debug then
1694: execute immediate l_stmt1;
1695: if g_debug then
1696: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
1697: end if;
1698: EDW_OWB_COLLECTION_UTIL.alter_session('NO-PARALLEL');
1699: l_stmt1:='select row_id from '||l_table;
1700: open cv for l_stmt1;
1701: loop
1702: fetch cv into l_rowid;
1710: end if;
1711: end loop;
1712: commit;
1713: close cv;
1714: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table)=false then
1715: null;
1716: end if;
1717: if g_parallel is not null then
1718: EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
1714: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table)=false then
1715: null;
1716: end if;
1717: if g_parallel is not null then
1718: EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
1719: end if;
1720: else
1721: begin
1722: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1718: EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
1719: end if;
1720: else
1721: begin
1722: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1723: execute immediate l_stmt;
1724: exception when others then
1725: if sqlcode=-4030 then
1726: if g_debug then
1761: return;
1762: end if;
1763: end if;
1764: --update the row in progress log table. EDWACOLB does the insert
1765: if EDW_OWB_COLLECTION_UTIL.record_coll_progress(g_dim_name, g_object_type,
1766: g_number_rows_processed,'PROCESSING','UPDATE')= false then
1767: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
1768: g_status:=false;
1769: write_to_log_file_n(g_status_message);
1763: end if;
1764: --update the row in progress log table. EDWACOLB does the insert
1765: if EDW_OWB_COLLECTION_UTIL.record_coll_progress(g_dim_name, g_object_type,
1766: g_number_rows_processed,'PROCESSING','UPDATE')= false then
1767: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
1768: g_status:=false;
1769: write_to_log_file_n(g_status_message);
1770: end if;
1771: commit;
1806: end if;
1807: if g_debug then
1808: write_to_log_file_n('Going to execute '||l_stmt);
1809: end if;
1810: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1811: execute immediate l_stmt;
1812: l_count:=sql%rowcount;
1813: commit;
1814: if g_debug then
1853: l_stmt:=l_stmt||' as select row_id,decode(status,1,2,0,1,2) status from '||
1854: g_ilog_prev;
1855: end if;
1856: end if;
1857: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
1858: null;
1859: end if;
1860: if g_debug then
1861: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1863: execute immediate l_stmt;
1864: if g_debug then
1865: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
1866: end if;
1867: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_prev)=false then
1868: null;
1869: end if;
1870: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_ilog,' status=1 ')<2 then
1871: l_count:=0;
1866: end if;
1867: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_prev)=false then
1868: null;
1869: end if;
1870: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_ilog,' status=1 ')<2 then
1871: l_count:=0;
1872: else
1873: l_count:=1;
1874: end if;
1897: l_stmt:='delete '||g_ilog||' where status=1';
1898: if g_debug then
1899: write_to_log_file_n('Going to execute '||l_stmt);
1900: end if;
1901: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1902: execute immediate l_stmt;
1903: --commit;
1904: if g_debug then
1905: write_to_log_file_n('Deleted '||sql%rowcount||' rows from '||g_ilog||get_time);
1923: --g_dim_kl_table
1924: if g_debug then
1925: write_to_log_file_n('In create_kl_table');
1926: end if;
1927: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_kl_table_temp) = false then
1928: null;
1929: end if;
1930: l_dim_name_temp_int:=g_dim_name_temp_int||'T';--just a temp table
1931: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dim_name_temp_int) = false then
1927: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_kl_table_temp) = false then
1928: null;
1929: end if;
1930: l_dim_name_temp_int:=g_dim_name_temp_int||'T';--just a temp table
1931: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dim_name_temp_int) = false then
1932: null;
1933: end if;
1934: l_stmt:='create table '||l_dim_name_temp_int||' tablespace '||g_op_table_space;
1935: if g_parallel is not null then
1942: execute immediate l_stmt;
1943: if g_debug then
1944: write_to_log_file_n('Created '||l_dim_name_temp_int||' with '||sql%rowcount||' records '||get_time);
1945: end if;
1946: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dim_name_temp_int,instr(l_dim_name_temp_int,'.')+1,
1947: length(l_dim_name_temp_int)),substr(l_dim_name_temp_int,1,instr(l_dim_name_temp_int,'.')-1));
1948: l_stmt:='create table '||g_dim_kl_table_temp||' tablespace '||g_op_table_space;
1949: if g_parallel is not null then
1950: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
1970: write_to_log_file_n(g_status_message);
1971: write_to_log_file('Problem stmt '||l_stmt);
1972: return false;
1973: end;
1974: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_dim_kl_table_temp,instr(g_dim_kl_table_temp,'.')+1,
1975: length(g_dim_kl_table_temp)),substr(g_dim_kl_table_temp,1,instr(g_dim_kl_table_temp,'.')-1));
1976: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_kl_table) = false then
1977: null;
1978: end if;
1972: return false;
1973: end;
1974: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_dim_kl_table_temp,instr(g_dim_kl_table_temp,'.')+1,
1975: length(g_dim_kl_table_temp)),substr(g_dim_kl_table_temp,1,instr(g_dim_kl_table_temp,'.')-1));
1976: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_kl_table) = false then
1977: null;
1978: end if;
1979: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dim_name_temp_int) = false then
1980: null;
1975: length(g_dim_kl_table_temp)),substr(g_dim_kl_table_temp,1,instr(g_dim_kl_table_temp,'.')-1));
1976: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_kl_table) = false then
1977: null;
1978: end if;
1979: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dim_name_temp_int) = false then
1980: null;
1981: end if;
1982: l_stmt:='create table '||g_dim_kl_table||' tablespace '||g_op_table_space;
1983: if g_parallel is not null then
2008: write_to_log_file('Problem stmt '||l_stmt);
2009: return false;
2010: end;
2011: --analyze the table
2012: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_dim_kl_table,instr(g_dim_kl_table,'.')+1,
2013: length(g_dim_kl_table)),substr(g_dim_kl_table,1,instr(g_dim_kl_table,'.')-1));
2014: if g_debug then
2015: write_to_log_file_n('Table '||g_dim_kl_table||' analyzed ');
2016: end if;
2038: if g_debug then
2039: write_to_log_file_n('In create_temp_table');
2040: end if;
2041: --is the table there? then drop it.
2042: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_temp) = false then
2043: write_to_log_file_n('Table '||g_dim_name_temp||' not found for dropping');
2044: end if;
2045: if g_parallel is null then
2046: l_divide:=2;
2134: if g_debug then
2135: write_to_log_file_n('In create_temp_table_int');
2136: end if;
2137: --is the table there? then drop it.
2138: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_temp_int) = false then
2139: write_to_log_file_n('Table '||g_dim_name_temp_int||' not found for dropping');
2140: end if;
2141: l_stmt:='create table '||g_dim_name_temp_int||' tablespace '||g_op_table_space;
2142: if g_parallel is not null then
2329: Begin
2330: if g_debug then
2331: write_to_log_file_n('In create_g_dim_name_with_slow');
2332: end if;
2333: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_with_slow)=false then
2334: null;
2335: end if;
2336: if g_dim_count is null then
2337: g_dim_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_dim_name,g_table_owner);
2333: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_with_slow)=false then
2334: null;
2335: end if;
2336: if g_dim_count is null then
2337: g_dim_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_dim_name,g_table_owner);
2338: end if;
2339: if g_slow_implemented then
2340: l_kl_table:=g_dim_kl_table;
2341: else
2340: l_kl_table:=g_dim_kl_table;
2341: else
2342: l_kl_table:=g_dim_name_temp_int;
2343: end if;
2344: l_kl_table_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(l_kl_table,g_bis_owner);
2345: if g_debug then
2346: write_to_log_file_n('Dim count = '||g_dim_count||', kl table count='||l_kl_table_count);
2347: end if;
2348: l_use_nl:=true;
2346: write_to_log_file_n('Dim count = '||g_dim_count||', kl table count='||l_kl_table_count);
2347: end if;
2348: l_use_nl:=true;
2349: if g_dim_count>0 then
2350: l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_kl_table_count,g_dim_count,g_join_nl_percentage);
2351: end if;
2352: l_stmt:='create table '||g_dim_name_with_slow||' tablespace '||g_op_table_space;
2353: if g_parallel is not null then
2354: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2379: execute immediate l_stmt;
2380: if g_debug then
2381: write_to_log_file_n('Created '||g_dim_name_with_slow||' with '||sql%rowcount||' rows '||get_time);
2382: end if;
2383: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_dim_name_with_slow,instr(g_dim_name_with_slow,'.')+1,
2384: length(g_dim_name_with_slow)),substr(g_dim_name_with_slow,1,instr(g_dim_name_with_slow,'.')-1));
2385: return true;
2386: Exception when others then
2387: g_status_message:=sqlerrm;
2408: write_to_log_file_n('commit');
2409: end if;
2410: --analyze the table
2411: if sql%rowcount > 0 then
2412: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_dim_name_temp,instr(g_dim_name_temp,'.')+1,
2413: length(g_dim_name_temp)),substr(g_dim_name_temp,1,instr(g_dim_name_temp,'.')-1));
2414: end if;
2415: commit;
2416: if g_debug then
2499: procedure count_temp_table_records is
2500: l_stmt varchar2(2000);
2501: TYPE CurTyp IS REF CURSOR;
2502: cv CurTyp;
2503: l_operation_code1 EDW_OWB_COLLECTION_UTIL.varcharTableType;
2504: l_operation_code2 EDW_OWB_COLLECTION_UTIL.varcharTableType;
2505: l_count EDW_OWB_COLLECTION_UTIL.numberTableType;
2506: l_rowid EDW_OWB_COLLECTION_UTIL.rowidTableType;
2507: l_number number;
2500: l_stmt varchar2(2000);
2501: TYPE CurTyp IS REF CURSOR;
2502: cv CurTyp;
2503: l_operation_code1 EDW_OWB_COLLECTION_UTIL.varcharTableType;
2504: l_operation_code2 EDW_OWB_COLLECTION_UTIL.varcharTableType;
2505: l_count EDW_OWB_COLLECTION_UTIL.numberTableType;
2506: l_rowid EDW_OWB_COLLECTION_UTIL.rowidTableType;
2507: l_number number;
2508: begin
2501: TYPE CurTyp IS REF CURSOR;
2502: cv CurTyp;
2503: l_operation_code1 EDW_OWB_COLLECTION_UTIL.varcharTableType;
2504: l_operation_code2 EDW_OWB_COLLECTION_UTIL.varcharTableType;
2505: l_count EDW_OWB_COLLECTION_UTIL.numberTableType;
2506: l_rowid EDW_OWB_COLLECTION_UTIL.rowidTableType;
2507: l_number number;
2508: begin
2509: if g_debug then
2502: cv CurTyp;
2503: l_operation_code1 EDW_OWB_COLLECTION_UTIL.varcharTableType;
2504: l_operation_code2 EDW_OWB_COLLECTION_UTIL.varcharTableType;
2505: l_count EDW_OWB_COLLECTION_UTIL.numberTableType;
2506: l_rowid EDW_OWB_COLLECTION_UTIL.rowidTableType;
2507: l_number number;
2508: begin
2509: if g_debug then
2510: write_to_log_file_n('In count_temp_table_records');
2533: End ;
2534:
2535:
2536: PROCEDURE identify_slow_cols IS
2537: l_table EDW_OWB_COLLECTION_UTIL.varcharTableType;
2538: l_table_count number:=1;
2539: cursor c4(p_dim_id number) is
2540: select relation.sequence_name
2541: from edw_pvt_sequences_md_v relation,
2550: Begin
2551: if g_debug then
2552: write_to_log_file_n('In identify_slow_cols '||get_time);
2553: end if;
2554: if EDW_OWB_COLLECTION_UTIL.is_slow_change_implemented(g_dim_name,g_slow_is_name)= true then
2555: g_slow_implemented:=true;
2556: if g_debug then
2557: write_to_log_file_n('Slowly changing implemented');
2558: end if;
2575: g_status:=false;
2576: return;
2577: end if;
2578: else
2579: if EDW_OWB_COLLECTION_UTIL.get_item_set_cols(l_table,l_table_count,g_dim_name,g_slow_is_name)=false then
2580: g_status:=false;
2581: return;
2582: end if;
2583: end if;
2772: if g_debug then
2773: write_to_log_file_n('In execute_hold_insert_stmt'||get_time);
2774: end if;
2775: l_create_type:='MASS';
2776: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_hold)=false then
2777: null;
2778: end if;
2779: <
2780: if g_ltc_merge_use_nl=true or l_create_type='ROW-BY-ROW' then
2800: end if;
2801: if g_debug then
2802: write_to_log_file_n('Going to execute '||l_stmt||get_time);
2803: end if;
2804: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table)=false then
2805: null;
2806: end if;
2807: execute immediate l_stmt;
2808: if g_debug then
2810: end if;
2811: l_count:=0;
2812: l_total_count:=0;
2813: --disable parallel dml
2814: EDW_OWB_COLLECTION_UTIL.alter_session('NO-PARALLEL');
2815: l_stmt:='select row_id from '||l_table;
2816: if g_debug then
2817: write_to_log_file_n('Going to execute '||l_stmt||get_time);
2818: end if;
2832: commit;
2833: if g_debug then
2834: write_to_log_file_n('Inserted '||l_total_count||' rows into '||g_dim_name_hold||get_time);
2835: end if;
2836: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table)=false then
2837: null;
2838: end if;
2839: if g_parallel is not null then
2840: EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
2836: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table)=false then
2837: null;
2838: end if;
2839: if g_parallel is not null then
2840: EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
2841: end if;
2842: else
2843: begin
2844: execute immediate g_hold_insert_stmt;
2862: end if;
2863: if create_hold_index=false then
2864: null;
2865: end if;
2866: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_dim_name_hold,instr(g_dim_name_hold,'.')+1,
2867: length(g_dim_name_hold)),substr(g_dim_name_hold,1,instr(g_dim_name_hold,'.')-1));
2868: Exception when others then
2869: g_status_message:=sqlerrm;
2870: write_to_log_file_n(g_status_message);
2876: Begin
2877: if g_debug then
2878: write_to_log_file_n('In create_dim_name_rowid_hold');
2879: end if;
2880: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_rowid_hold)=false then
2881: null;
2882: end if;
2883: l_stmt:='create table '||g_dim_name_rowid_hold||' tablespace '||g_op_table_space;
2884: if g_parallel is not null then
2891: execute immediate l_stmt;
2892: if g_debug then
2893: write_to_log_file_n('Created '||g_dim_name_rowid_hold||' with '||sql%rowcount||' rows '||get_time);
2894: end if;
2895: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_dim_name_rowid_hold,instr(g_dim_name_rowid_hold,'.')+1,
2896: length(g_dim_name_rowid_hold)),substr(g_dim_name_rowid_hold,1,instr(g_dim_name_rowid_hold,'.')-1));
2897: return true;
2898: Exception when others then
2899: g_status_message:=sqlerrm;
2906: function execute_update_stmt return number is
2907: l_stmt varchar2(5000);
2908: TYPE CurTyp IS REF CURSOR;
2909: cv CurTyp;
2910: l_rowid EDW_OWB_COLLECTION_UTIL.rowidTableType;
2911: l_count number;
2912: l_total_count number:=0;
2913: l_update_type varchar2(400);
2914: Begin
2961: begin
2962: if g_debug then
2963: write_to_log_file_n('Going to execute g_update_stmt_star '||get_time);
2964: end if;
2965: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
2966: execute immediate g_update_stmt_star;
2967: l_total_count:=sql%rowcount;
2968: exception when others then
2969: if sqlcode=-4030 then
2993: begin
2994: if g_debug then
2995: write_to_log_file_n('Going to execute '||l_stmt||get_time);
2996: end if;
2997: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
2998: execute immediate l_stmt;
2999: if g_debug then
3000: write_to_log_file('Deleted '||sql%rowcount||' rows');
3001: end if;
3009: begin
3010: if g_debug then
3011: write_to_log_file_n('Going to execute g_update_stmt_star');
3012: end if;
3013: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
3014: execute immediate g_update_stmt_star;
3015: if g_debug then
3016: write_to_log_file('Inserted '||sql%rowcount||' rows');
3017: end if;
3026: end if;
3027: if g_debug then
3028: write_to_log_file_n('Updated '||l_total_count||' records In Star Table '||get_time);
3029: end if;
3030: --if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_rowid_hold)=false then
3031: --null;
3032: --end if;
3033: return l_total_count;
3034: EXCEPTION when others then
3129: end if;
3130: if g_debug then
3131: write_to_log_file_n('Going to execute '||l_stmt||get_time);
3132: end if;
3133: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table)=false then
3134: null;
3135: end if;
3136: execute immediate l_stmt;
3137: if g_debug then
3136: execute immediate l_stmt;
3137: if g_debug then
3138: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
3139: end if;
3140: EDW_OWB_COLLECTION_UTIL.alter_session('NO-PARALLEL');
3141: l_stmt:='select row_id from '||l_table;
3142: if g_debug then
3143: write_to_log_file_n('Going to execute '||l_stmt||get_time);
3144: end if;
3163: end;
3164: end loop;
3165: close cv;
3166: commit;
3167: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table)=false then
3168: null;
3169: end if;
3170: if g_parallel is not null then
3171: EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
3167: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table)=false then
3168: null;
3169: end if;
3170: if g_parallel is not null then
3171: EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
3172: end if;
3173: if l_unique_violation then
3174: if reset_temp_opcode=false then
3175: return false;
3179: if g_debug then
3180: write_to_log_file_n('going to execute g_insert_stmt_star ');
3181: end if;
3182: begin
3183: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
3184: execute immediate g_insert_stmt_star;
3185: g_number_rows_inserted:=sql%rowcount;
3186: exception when others then
3187: rollback;
3260: fetch c3 into g_dim_pk,g_dim_user_pk,g_dim_id;
3261: close c3;
3262: if g_dim_pk is null then
3263: --user did not define a PK constraint on dim start table
3264: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_NO_PRIMARY_KEY_FOUND');
3265: write_to_log_file_n(g_status_message);
3266: g_status:=false;
3267: return;
3268: end if;
3337: Begin
3338: if g_debug then
3339: write_to_log_file_n('In get_lvl_relations'||get_time);
3340: end if;
3341: EDW_OWB_COLLECTION_UTIL.Get_lvl_dim_mapping(
3342: g_dim_col,
3343: g_level_name,
3344: g_level_col,
3345: g_number_mapping,
3344: g_level_col,
3345: g_number_mapping,
3346: 1); --dont find the rowid
3347: if g_number_mapping=0 then
3348: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_NO_LVL_DIM_MAPPING_FOUND');
3349: write_to_log_file_n(g_status_message);
3350: g_status:=false;
3351: return;
3352: end if;
3350: g_status:=false;
3351: return;
3352: end if;
3353: --get the lowest level and its relation id
3354: EDW_OWB_COLLECTION_UTIL.get_lowest_level(g_lowest_level, g_lowest_level_id);
3355: if g_lowest_level is null then
3356: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_NO_LOWEST_LEVEL_FOUND');
3357: write_to_log_file_n(g_status_message);
3358: g_status:=false;
3352: end if;
3353: --get the lowest level and its relation id
3354: EDW_OWB_COLLECTION_UTIL.get_lowest_level(g_lowest_level, g_lowest_level_id);
3355: if g_lowest_level is null then
3356: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_NO_LOWEST_LEVEL_FOUND');
3357: write_to_log_file_n(g_status_message);
3358: g_status:=false;
3359: return;
3360: end if;
3366: g_lowest_level_index:=i;
3367: end if;
3368: end loop;
3369: if g_lowest_level_alias is null then
3370: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_NO_LOWEST_LEVEL_FOUND');
3371: write_to_log_file_n(g_status_message);
3372: g_status:=false;
3373: return;
3374: end if;
3403: procedure make_select_from_where_stmt is
3404: l_run integer:=0;
3405: l_all_count integer:=0;
3406: first_time boolean;
3407: l_alias EDW_OWB_COLLECTION_UTIL.varcharTableType;
3408: child_alias varchar2(100);
3409: l_look_at EDW_OWB_COLLECTION_UTIL.booleanTableType;
3410: l_looked_at EDW_OWB_COLLECTION_UTIL.booleanTableType;
3411: l_start number;
3405: l_all_count integer:=0;
3406: first_time boolean;
3407: l_alias EDW_OWB_COLLECTION_UTIL.varcharTableType;
3408: child_alias varchar2(100);
3409: l_look_at EDW_OWB_COLLECTION_UTIL.booleanTableType;
3410: l_looked_at EDW_OWB_COLLECTION_UTIL.booleanTableType;
3411: l_start number;
3412: l_done_flag boolean;
3413: l_consider_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
3406: first_time boolean;
3407: l_alias EDW_OWB_COLLECTION_UTIL.varcharTableType;
3408: child_alias varchar2(100);
3409: l_look_at EDW_OWB_COLLECTION_UTIL.booleanTableType;
3410: l_looked_at EDW_OWB_COLLECTION_UTIL.booleanTableType;
3411: l_start number;
3412: l_done_flag boolean;
3413: l_consider_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
3414: l_number_consider_level number:=0;
3409: l_look_at EDW_OWB_COLLECTION_UTIL.booleanTableType;
3410: l_looked_at EDW_OWB_COLLECTION_UTIL.booleanTableType;
3411: l_start number;
3412: l_done_flag boolean;
3413: l_consider_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
3414: l_number_consider_level number:=0;
3415: l_level_index number;
3416: l_child_index number;
3417: l_affected_levels EDW_OWB_COLLECTION_UTIL.varcharTableType;
3413: l_consider_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
3414: l_number_consider_level number:=0;
3415: l_level_index number;
3416: l_child_index number;
3417: l_affected_levels EDW_OWB_COLLECTION_UTIL.varcharTableType;
3418: l_number_affected_levels number;
3419: Begin
3420: --make the alias
3421: if g_debug then
3441: g_consider_level(l_start):=true;
3442: l_level_index:=0;
3443: for j in 1..g_child_level_number(l_start) loop
3444: l_run:=l_run+1;
3445: l_level_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,g_child_levels(l_run));
3446: if EDW_OWB_COLLECTION_UTIL.value_in_table(l_consider_level,l_number_consider_level,
3447: g_child_levels(l_run)) then
3448: l_looked_at(l_start):=true;
3449: exit;
3442: l_level_index:=0;
3443: for j in 1..g_child_level_number(l_start) loop
3444: l_run:=l_run+1;
3445: l_level_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,g_child_levels(l_run));
3446: if EDW_OWB_COLLECTION_UTIL.value_in_table(l_consider_level,l_number_consider_level,
3447: g_child_levels(l_run)) then
3448: l_looked_at(l_start):=true;
3449: exit;
3450: elsif l_look_at(l_level_index) then
3489: g_consider_level(i):=true;
3490: end loop;
3491: else
3492: for i in 1..l_number_affected_levels loop
3493: g_consider_level(EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,
3494: l_affected_levels(i))):=true;
3495: end loop;
3496: end if;
3497: else
3499: for i in 1..g_number_levels loop
3500: g_consider_level(i):=true;
3501: end loop;
3502: end if;
3503: l_level_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,g_lowest_level_global);
3504: if g_consider_level(l_level_index)=false then
3505: if g_debug then
3506: write_to_log_file_n('Lowest level not in. something fishy');
3507: end if;
3614: exit;
3615: end if;
3616: end loop;
3617: if l_child_index=0 then
3618: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_NO_LEVEL_FOUND');
3619: write_to_log_file_n(g_status_message);
3620: g_status:=false;
3621: return;
3622: end if;
3695: procedure make_select_from_where_ins is
3696: l_run integer:=0;
3697: l_all_count integer:=0;
3698: first_time boolean;
3699: l_alias EDW_OWB_COLLECTION_UTIL.varcharTableType;
3700: child_alias varchar2(100);
3701: l_child_index number;
3702: Begin
3703: --make the alias
3777: exit;
3778: end if;
3779: end loop;
3780: if l_child_index=0 then
3781: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_NO_LEVEL_FOUND');
3782: write_to_log_file_n(g_status_message);
3783: g_status:=false;
3784: return;
3785: end if;
3850: Begin
3851: if g_debug then
3852: write_to_log_file_n('In create_gilog_T');
3853: end if;
3854: if EDW_OWB_COLLECTION_UTIL.drop_table(p_ilog_temp)=false then
3855: null;
3856: end if;
3857: l_stmt:='create table '||p_ilog_temp||'(row_id rowid) '||' tablespace '||g_op_table_space;
3858: if g_parallel is not null then
3877: if g_debug then
3878: write_to_log_file_n('Going to execute '||l_stmt);
3879: end if;
3880: begin
3881: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
3882: execute immediate l_stmt;
3883: if g_debug then
3884: write_to_log_file_n('Moved '||sql%rowcount||' rows into '||p_ilog_temp);
3885: end if;
3883: if g_debug then
3884: write_to_log_file_n('Moved '||sql%rowcount||' rows into '||p_ilog_temp);
3885: end if;
3886: commit;
3887: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(p_ilog_temp,instr(p_ilog_temp,'.')+1,
3888: length(p_ilog_temp)),substr(p_ilog_temp,1,instr(p_ilog_temp,'.')-1));
3889: exception when others then
3890: g_status:=false;
3891: g_status_message:=sqlerrm;
3905: */
3906: function create_ltc_ilog_table(p_mode varchar2) return boolean is
3907: l_stmt varchar2(10000);
3908: l_pk varchar2(400);
3909: l_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
3910: l_number_fk number;
3911: l_found boolean:=false;
3912: l_consider boolean;
3913: l_create_ilog_index boolean;
3938: if g_consider_level(i) then
3939: l_consider:=true;
3940: end if;
3941: elsif p_mode='NON-SNP' then
3942: if EDW_OWB_COLLECTION_UTIL.check_table(g_levels_I(i))=false then
3943: l_consider:=true;
3944: end if;
3945: end if;
3946: if g_debug then
3978: for j in 1..l_number_fk loop
3979: write_to_log_file(l_fk(j));
3980: end loop;
3981: end if;
3982: if EDW_OWB_COLLECTION_UTIL.drop_table(g_levels_I(i))=false then
3983: null;
3984: end if;
3985: l_ltc_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(i),g_table_owner);
3986: if g_debug then
3981: end if;
3982: if EDW_OWB_COLLECTION_UTIL.drop_table(g_levels_I(i))=false then
3983: null;
3984: end if;
3985: l_ltc_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(i),g_table_owner);
3986: if g_debug then
3987: write_to_log_file_n('LTC count='||l_ltc_count);
3988: end if;
3989: l_storage:=4;
4013: if g_debug then
4014: write_to_log_file_n('Going to execute '||l_stmt||get_time);
4015: end if;
4016: l_create_ilog_index:=true;
4017: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
4018: begin
4019: execute immediate l_stmt;
4020: l_total_count:=sql%rowcount;
4021: if g_debug then
4036: return false;
4037: end if;
4038: end;
4039: if l_create_ilog_index then
4040: l_create_ilog_index:=EDW_OWB_COLLECTION_UTIL.get_join_nl(g_collection_size,l_total_count,
4041: g_join_nl_percentage);
4042: if l_create_ilog_index then
4043: if l_pk is not null then
4044: l_stmt:='create unique index '||g_levels_I(i)||'u1 on '||g_levels_I(i)||'('||l_pk||')';
4060: write_to_log_file_n('Going to execute '||l_stmt||get_time);
4061: end if;
4062: execute immediate l_stmt;
4063: end if;
4064: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_levels_I(i),instr(g_levels_I(i),'.')+1,
4065: length(g_levels_I(i))),substr(g_levels_I(i),1,instr(g_levels_I(i),'.')-1));
4066: end if;
4067: end if;--if l_consider then
4068: else
4105: l_stmt:=l_stmt||' distinct '||g_ltc_pk||' from '||g_level_snapshot_logs(i);
4106: else
4107: l_stmt:=l_stmt||' distinct m_row$$ row_id from '||g_level_snapshot_logs(i);
4108: end if;
4109: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table)=false then
4110: null;
4111: end if;
4112: if g_debug then
4113: write_to_log_file_n('Going to execute '||l_stmt||get_time);
4116: l_count:=sql%rowcount;
4117: if g_debug then
4118: write_to_log_file_n('Created with '||l_count||' rows '||get_time);
4119: end if;
4120: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_table,instr(l_table,'.')+1,
4121: length(l_table)),substr(l_table,1,instr(l_table,'.')-1));
4122: if l_count>0 then
4123: l_ltc_i_found:=false;
4124: if not(g_levels(i)=g_lowest_level_global and g_ll_snplog_has_pk) then
4123: l_ltc_i_found:=false;
4124: if not(g_levels(i)=g_lowest_level_global and g_ll_snplog_has_pk) then
4125: --if g_levels_I(i)<>g_levels(i) then
4126: if g_use_ltc_ilog(i) then
4127: if EDW_OWB_COLLECTION_UTIL.check_table(g_levels_I(i)) then
4128: l_ltc_i_found:=true;
4129: end if;
4130: end if;
4131: end if;
4130: end if;
4131: end if;
4132: end if;
4133: <
4134: if EDW_OWB_COLLECTION_UTIL.drop_table(g_snplogs_L(i))=false then
4135: null;
4136: end if;
4137: /*l_stmt:='create table '||g_snplogs_L(i)||' tablespace '||g_op_table_space;
4138: l_stmt:=l_stmt||' storage (initial 4M next 4M pctincrease 0) ';
4163: if g_parallel is not null then
4164: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
4165: end if;
4166: if g_level_count(i) is null then
4167: g_level_count(i):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(i),g_table_owner);
4168: end if;
4169: l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_count,g_level_count(i),g_join_nl_percentage);
4170: if g_levels(i)=g_lowest_level_global and g_ll_snplog_has_pk then
4171: l_stmt:=l_stmt||' as select ';
4165: end if;
4166: if g_level_count(i) is null then
4167: g_level_count(i):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(i),g_table_owner);
4168: end if;
4169: l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_count,g_level_count(i),g_join_nl_percentage);
4170: if g_levels(i)=g_lowest_level_global and g_ll_snplog_has_pk then
4171: l_stmt:=l_stmt||' as select ';
4172: if l_use_ordered_hint then
4173: l_stmt:=l_stmt||'/*+ordered ';
4207: if g_debug then
4208: write_to_log_file_n('Going to execute '||l_stmt||get_time);
4209: end if;
4210: begin
4211: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
4212: execute immediate l_stmt;
4213: if g_debug then
4214: write_to_log_file_n('Inserted into '||g_snplogs_L(i)||' '||sql%rowcount||' rows '||get_time);
4215: end if;
4245: if g_debug then
4246: write_to_log_file_n('Created '||g_snplogs_L(i)||get_time);
4247: end if;
4248: end if;
4249: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_snplogs_L(i),instr(g_snplogs_L(i),'.')+1,
4250: length(g_snplogs_L(i))),substr(g_snplogs_L(i),1,instr(g_snplogs_L(i),'.')-1));
4251: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table)=false then
4252: null;
4253: end if;
4247: end if;
4248: end if;
4249: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_snplogs_L(i),instr(g_snplogs_L(i),'.')+1,
4250: length(g_snplogs_L(i))),substr(g_snplogs_L(i),1,instr(g_snplogs_L(i),'.')-1));
4251: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table)=false then
4252: null;
4253: end if;
4254: end loop;
4255: return true;
4323: exit;
4324: end if;
4325: end loop;
4326: if p_mode='DOWN' then
4327: l_L_table_found:=EDW_OWB_COLLECTION_UTIL.check_table(g_snplogs_L(l_child_index));
4328: if g_debug then
4329: if l_L_table_found then
4330: write_to_log_file_n('The L table '||g_snplogs_L(l_child_index)||' found');
4331: else
4333: end if;
4334: end if;
4335: l_a1_key:=g_fk_pk_child_fk(l_fk_pk_index);
4336: l_a2_key:=g_fk_pk_parent_pk(l_fk_pk_index);
4337: l_L_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_snplogs_L(l_parent_index),g_bis_owner);
4338: if g_levels_I(l_child_index)=g_levels(l_child_index) then
4339: if g_level_count(l_child_index) is null then
4340: g_level_count(l_child_index):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(l_child_index),
4341: g_table_owner);
4336: l_a2_key:=g_fk_pk_parent_pk(l_fk_pk_index);
4337: l_L_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_snplogs_L(l_parent_index),g_bis_owner);
4338: if g_levels_I(l_child_index)=g_levels(l_child_index) then
4339: if g_level_count(l_child_index) is null then
4340: g_level_count(l_child_index):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(l_child_index),
4341: g_table_owner);
4342: end if;
4343: --check index on l_a1_key
4344: if EDW_OWB_COLLECTION_UTIL.check_index_on_column(g_levels(l_child_index),g_table_owner,l_a1_key) then
4340: g_level_count(l_child_index):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(l_child_index),
4341: g_table_owner);
4342: end if;
4343: --check index on l_a1_key
4344: if EDW_OWB_COLLECTION_UTIL.check_index_on_column(g_levels(l_child_index),g_table_owner,l_a1_key) then
4345: l_use_nl_child:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_L_count,g_level_count(l_child_index),
4346: g_join_nl_percentage);
4347: end if;
4348: end if;
4341: g_table_owner);
4342: end if;
4343: --check index on l_a1_key
4344: if EDW_OWB_COLLECTION_UTIL.check_index_on_column(g_levels(l_child_index),g_table_owner,l_a1_key) then
4345: l_use_nl_child:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_L_count,g_level_count(l_child_index),
4346: g_join_nl_percentage);
4347: end if;
4348: end if;
4349: if g_levels_I(l_parent_index)=g_levels(l_parent_index) then
4347: end if;
4348: end if;
4349: if g_levels_I(l_parent_index)=g_levels(l_parent_index) then
4350: if g_level_count(l_parent_index) is null then
4351: g_level_count(l_parent_index):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(l_parent_index),
4352: g_table_owner);
4353: end if;
4354: l_use_nl_parent:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_L_count,g_level_count(l_parent_index),
4355: g_join_nl_percentage);
4350: if g_level_count(l_parent_index) is null then
4351: g_level_count(l_parent_index):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(l_parent_index),
4352: g_table_owner);
4353: end if;
4354: l_use_nl_parent:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_L_count,g_level_count(l_parent_index),
4355: g_join_nl_percentage);
4356: end if;
4357: if l_L_table_found then
4358: l_stmt:='create table '||g_snplogs_LT(l_child_index)||' tablespace '||g_op_table_space;
4379: end if;
4380: l_stmt:=l_stmt||' from '||g_snplogs_L(l_parent_index)||' Y,'||g_levels_I(l_parent_index)||' A2,'||
4381: g_levels_I(l_child_index)||' A1 where A2.'||l_rowid_parent||'=Y.row_id and A1.'||l_a1_key||'=A2.'||l_a2_key;
4382: else
4383: l_L_table_found:=EDW_OWB_COLLECTION_UTIL.check_table(g_snplogs_L(l_parent_index));
4384: if g_debug then
4385: if l_L_table_found then
4386: write_to_log_file_n('The L table '||g_snplogs_L(l_parent_index)||' found');
4387: else
4389: end if;
4390: end if;
4391: l_a1_key:=g_fk_pk_child_fk(l_fk_pk_index);
4392: l_a2_key:=g_fk_pk_parent_pk(l_fk_pk_index);
4393: l_L_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_snplogs_L(l_child_index),g_bis_owner);
4394: if g_levels_I(l_child_index)=g_levels(l_child_index) then
4395: if g_level_count(l_child_index) is null then
4396: g_level_count(l_child_index):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(l_child_index),
4397: g_table_owner);
4392: l_a2_key:=g_fk_pk_parent_pk(l_fk_pk_index);
4393: l_L_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_snplogs_L(l_child_index),g_bis_owner);
4394: if g_levels_I(l_child_index)=g_levels(l_child_index) then
4395: if g_level_count(l_child_index) is null then
4396: g_level_count(l_child_index):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(l_child_index),
4397: g_table_owner);
4398: end if;
4399: l_use_nl_child:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_L_count,g_level_count(l_child_index),
4400: g_join_nl_percentage);
4395: if g_level_count(l_child_index) is null then
4396: g_level_count(l_child_index):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(l_child_index),
4397: g_table_owner);
4398: end if;
4399: l_use_nl_child:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_L_count,g_level_count(l_child_index),
4400: g_join_nl_percentage);
4401: end if;
4402: --first create a fk table
4403: l_fk_table:=g_snplogs_L(l_parent_index)||'F';
4414: l_stmt:=l_stmt||'/*+parallel(A1,'||g_parallel||')*/ ';
4415: end if;
4416: l_stmt:=l_stmt||' distinct A1.'||l_a1_key||' from '||g_snplogs_L(l_child_index)||' Y,'||
4417: g_levels_I(l_child_index)||' A1 where A1.'||l_rowid_child||'=Y.row_id';
4418: if EDW_OWB_COLLECTION_UTIL.drop_table(l_fk_table)=false then
4419: null;
4420: end if;
4421: if g_debug then
4422: write_to_log_file_n('Going to execute '||l_stmt||get_time);
4425: l_fk_table_count:=sql%rowcount;
4426: if g_debug then
4427: write_to_log_file_n('Created '||l_fk_table||' with '||sql%rowcount||' rows '||get_time);
4428: end if;
4429: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_fk_table,instr(l_fk_table,'.')+1,
4430: length(l_fk_table)),substr(l_fk_table,1,instr(l_fk_table,'.')-1));
4431: if g_levels_I(l_parent_index)=g_levels(l_parent_index) then
4432: if g_level_count(l_parent_index) is null then
4433: g_level_count(l_parent_index):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(l_parent_index),
4429: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_fk_table,instr(l_fk_table,'.')+1,
4430: length(l_fk_table)),substr(l_fk_table,1,instr(l_fk_table,'.')-1));
4431: if g_levels_I(l_parent_index)=g_levels(l_parent_index) then
4432: if g_level_count(l_parent_index) is null then
4433: g_level_count(l_parent_index):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(l_parent_index),
4434: g_table_owner);
4435: end if;
4436: if EDW_OWB_COLLECTION_UTIL.check_index_on_column(g_levels(l_parent_index),g_table_owner,l_a2_key) then
4437: l_use_nl_parent:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_fk_table_count,g_level_count(l_parent_index),
4432: if g_level_count(l_parent_index) is null then
4433: g_level_count(l_parent_index):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(l_parent_index),
4434: g_table_owner);
4435: end if;
4436: if EDW_OWB_COLLECTION_UTIL.check_index_on_column(g_levels(l_parent_index),g_table_owner,l_a2_key) then
4437: l_use_nl_parent:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_fk_table_count,g_level_count(l_parent_index),
4438: g_join_nl_percentage);
4439: end if;
4440: end if;
4433: g_level_count(l_parent_index):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(l_parent_index),
4434: g_table_owner);
4435: end if;
4436: if EDW_OWB_COLLECTION_UTIL.check_index_on_column(g_levels(l_parent_index),g_table_owner,l_a2_key) then
4437: l_use_nl_parent:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_fk_table_count,g_level_count(l_parent_index),
4438: g_join_nl_percentage);
4439: end if;
4440: end if;
4441: if l_L_table_found then
4477: write_to_log_file('child level '||g_levels(l_child_index));
4478: write_to_log_file('l_a1_key='||l_a1_key);
4479: write_to_log_file('l_a2_key='||l_a2_key);
4480: end if;
4481: if EDW_OWB_COLLECTION_UTIL.drop_table(g_snplogs_LT(l_child_index))=false then
4482: null;
4483: end if;
4484: if g_debug then
4485: write_to_log_file_n('Going to execute '||l_stmt||get_time);
4483: end if;
4484: if g_debug then
4485: write_to_log_file_n('Going to execute '||l_stmt||get_time);
4486: end if;
4487: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
4488: execute immediate l_stmt;
4489: if g_debug then
4490: if l_L_table_found then
4491: write_to_log_file_n('Created '||g_snplogs_LT(l_child_index)||' with '||sql%rowcount||' rows '||get_time);
4494: end if;
4495: end if;
4496: commit;
4497: if l_L_table_found then
4498: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_snplogs_LT(l_child_index),
4499: instr(g_snplogs_LT(l_child_index),'.')+1,length(g_snplogs_LT(l_child_index))),
4500: substr(g_snplogs_LT(l_child_index),1,instr(g_snplogs_LT(l_child_index),'.')-1));
4501: end if;
4502: --now insert into the L table
4507: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
4508: end if;
4509: l_stmt:=l_stmt||' as select row_id from '||g_snplogs_LT(l_child_index)||
4510: ' MINUS select row_id from '||g_snplogs_L(l_child_index);
4511: if EDW_OWB_COLLECTION_UTIL.drop_table(l_snplogs_L)=false then
4512: null;
4513: end if;
4514: if g_debug then
4515: write_to_log_file_n('Going to execute '||l_stmt||get_time);
4517: execute immediate l_stmt;
4518: if g_debug then
4519: write_to_log_file_n('Created '||l_snplogs_L||' with '||sql%rowcount||' rows '||get_time);
4520: end if;
4521: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_snplogs_L,instr(l_snplogs_L,'.')+1,
4522: length(l_snplogs_L)),substr(l_snplogs_L,1,instr(l_snplogs_L,'.')-1));
4523: if g_levels(l_child_index)=g_lowest_level_global and g_ll_snplog_has_pk then
4524: l_stmt:='insert into '||g_snplogs_L(l_child_index)||'(row_id,'||g_ltc_pk||') select A.row_id,A.'||g_ltc_pk||
4525: ' from '||l_snplogs_L||' B,'||g_snplogs_LT(l_child_index)||' A where A.row_id=B.row_id';
4528: end if;
4529: if g_debug then
4530: write_to_log_file_n('Going to execute '||l_stmt||get_time);
4531: end if;
4532: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
4533: execute immediate l_stmt;
4534: if g_debug then
4535: write_to_log_file_n('Inserted '||sql%rowcount||' rows into '||g_snplogs_L(l_child_index)||get_time);
4536: end if;
4534: if g_debug then
4535: write_to_log_file_n('Inserted '||sql%rowcount||' rows into '||g_snplogs_L(l_child_index)||get_time);
4536: end if;
4537: commit;
4538: if EDW_OWB_COLLECTION_UTIL.drop_table(l_snplogs_L)=false then
4539: null;
4540: end if;
4541: end if;
4542: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_snplogs_L(l_child_index),
4538: if EDW_OWB_COLLECTION_UTIL.drop_table(l_snplogs_L)=false then
4539: null;
4540: end if;
4541: end if;
4542: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_snplogs_L(l_child_index),
4543: instr(g_snplogs_L(l_child_index),'.')+1,length(g_snplogs_L(l_child_index))),
4544: substr(g_snplogs_L(l_child_index),1,instr(g_snplogs_L(l_child_index),'.')-1));
4545: if l_fk_table is not null then
4546: if EDW_OWB_COLLECTION_UTIL.drop_table(l_fk_table)=false then
4542: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_snplogs_L(l_child_index),
4543: instr(g_snplogs_L(l_child_index),'.')+1,length(g_snplogs_L(l_child_index))),
4544: substr(g_snplogs_L(l_child_index),1,instr(g_snplogs_L(l_child_index),'.')-1));
4545: if l_fk_table is not null then
4546: if EDW_OWB_COLLECTION_UTIL.drop_table(l_fk_table)=false then
4547: null;
4548: end if;
4549: end if;
4550: if EDW_OWB_COLLECTION_UTIL.drop_table(g_snplogs_LT(l_child_index))=false then
4546: if EDW_OWB_COLLECTION_UTIL.drop_table(l_fk_table)=false then
4547: null;
4548: end if;
4549: end if;
4550: if EDW_OWB_COLLECTION_UTIL.drop_table(g_snplogs_LT(l_child_index))=false then
4551: null;
4552: end if;
4553: return true;
4554: Exception when others then
4572: end if;
4573: for i in 1..g_fk_pk_number loop
4574: if g_fk_pk_child_level(i)=p_level then
4575: l_parent:=g_fk_pk_parent_level(i);
4576: l_parent_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,l_parent);
4577: l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,p_level);
4578: if g_consider_level(l_index) and g_consider_level(l_parent_index) then
4579: --if the parent L table has data then
4580: --if the parent level has been already considered, do not consider it again
4573: for i in 1..g_fk_pk_number loop
4574: if g_fk_pk_child_level(i)=p_level then
4575: l_parent:=g_fk_pk_parent_level(i);
4576: l_parent_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,l_parent);
4577: l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,p_level);
4578: if g_consider_level(l_index) and g_consider_level(l_parent_index) then
4579: --if the parent L table has data then
4580: --if the parent level has been already considered, do not consider it again
4581: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_considered_parent,g_number_considered_parent,
4577: l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,p_level);
4578: if g_consider_level(l_index) and g_consider_level(l_parent_index) then
4579: --if the parent L table has data then
4580: --if the parent level has been already considered, do not consider it again
4581: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_considered_parent,g_number_considered_parent,
4582: g_levels(l_parent_index))=false then
4583: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_snplogs_L(l_parent_index))= 2 then
4584: if insert_into_LT(p_level,g_levels(l_parent_index),'DOWN')=false then
4585: return false;
4579: --if the parent L table has data then
4580: --if the parent level has been already considered, do not consider it again
4581: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_considered_parent,g_number_considered_parent,
4582: g_levels(l_parent_index))=false then
4583: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_snplogs_L(l_parent_index))= 2 then
4584: if insert_into_LT(p_level,g_levels(l_parent_index),'DOWN')=false then
4585: return false;
4586: end if;
4587: g_number_considered_parent:=g_number_considered_parent+1;
4620: end if;
4621: for i in 1..g_fk_pk_number loop
4622: if g_fk_pk_child_level(i)=p_level then
4623: l_parent:=g_fk_pk_parent_level(i);
4624: l_parent_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,l_parent);
4625: l_child_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,p_level);
4626: if (p_mode='SNP' and g_consider_level(l_child_index) and g_consider_level(l_parent_index))
4627: OR p_mode='NON-SNP' then
4628: if g_debug then
4621: for i in 1..g_fk_pk_number loop
4622: if g_fk_pk_child_level(i)=p_level then
4623: l_parent:=g_fk_pk_parent_level(i);
4624: l_parent_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,l_parent);
4625: l_child_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,p_level);
4626: if (p_mode='SNP' and g_consider_level(l_child_index) and g_consider_level(l_parent_index))
4627: OR p_mode='NON-SNP' then
4628: if g_debug then
4629: write_to_log_file_n('Drill '||g_levels(l_child_index)||' to '||g_levels(l_parent_index));
4628: if g_debug then
4629: write_to_log_file_n('Drill '||g_levels(l_child_index)||' to '||g_levels(l_parent_index));
4630: end if;
4631: --if the parent level has been already considered, do not consider it again
4632: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_considered_parent,g_number_considered_parent,
4633: g_levels(l_parent_index))=false then
4634: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_snplogs_L(l_child_index))= 2 then
4635: if insert_into_LT(p_level,g_levels(l_parent_index),'UP')=false then
4636: return false;
4630: end if;
4631: --if the parent level has been already considered, do not consider it again
4632: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_considered_parent,g_number_considered_parent,
4633: g_levels(l_parent_index))=false then
4634: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_snplogs_L(l_child_index))= 2 then
4635: if insert_into_LT(p_level,g_levels(l_parent_index),'UP')=false then
4636: return false;
4637: end if;
4638: g_number_considered_parent:=g_number_considered_parent+1;
4670: Begin
4671: if g_debug then
4672: write_to_log_file_n('In insert_into_ilog_from_L');
4673: end if;
4674: l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,g_lowest_level_global);
4675: if EDW_OWB_COLLECTION_UTIL.check_table(g_ilog)=false then
4676: --if create_ilog_table= false then
4677: --return false;
4678: --end if;
4671: if g_debug then
4672: write_to_log_file_n('In insert_into_ilog_from_L');
4673: end if;
4674: l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,g_lowest_level_global);
4675: if EDW_OWB_COLLECTION_UTIL.check_table(g_ilog)=false then
4676: --if create_ilog_table= false then
4677: --return false;
4678: --end if;
4679: l_stmt:='create table '||g_ilog||' tablespace '||g_op_table_space;
4716: if g_debug then
4717: write_to_log_file_n('created with '||sql%rowcount||' rows '||get_time);
4718: end if;
4719: if p_multi_thread then
4720: edw_owb_collection_util.create_rownum_index_ilog(g_ilog,g_op_table_space,g_parallel);
4721: end if;
4722: else
4723: --must have pk_key in ilog for error recovery. there can be row migration with partitions
4724: --only effective if the lowest level snp log has pk_key
4743: end if;
4744: l_stmt:=l_stmt||' as select /*+ORDERED*/ B.row_id,B.status from '||
4745: g_ilog||' B,'||g_lowest_level_global||' A where A.rowid=B.row_id';
4746: end if;
4747: if EDW_OWB_COLLECTION_UTIL.drop_table(l_ilog_el)=false then
4748: null;
4749: end if;
4750: if g_debug then
4751: write_to_log_file_n('Going to execute '||l_stmt||get_time);
4753: execute immediate l_stmt;
4754: if g_debug then
4755: write_to_log_file_n('created '||l_ilog_el||' with '||sql%rowcount||' rows '||get_time);
4756: end if;
4757: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_ilog_el,instr(l_ilog_el,'.')+1,length(l_ilog_el)),
4758: substr(l_ilog_el,1,instr(l_ilog_el,'.')-1));
4759: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
4760: null;
4761: end if;
4755: write_to_log_file_n('created '||l_ilog_el||' with '||sql%rowcount||' rows '||get_time);
4756: end if;
4757: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_ilog_el,instr(l_ilog_el,'.')+1,length(l_ilog_el)),
4758: substr(l_ilog_el,1,instr(l_ilog_el,'.')-1));
4759: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
4760: null;
4761: end if;
4762: g_ilog:=l_ilog_el;
4763: l_ilog:=g_ilog||'T';
4771: else
4772: l_stmt:=l_stmt||' as select row_id,0 status from '||g_snplogs_L(l_index)||
4773: ' MINUS select row_id,0 status from '||g_ilog;
4774: end if;
4775: if EDW_OWB_COLLECTION_UTIL.drop_table(l_ilog)=false then
4776: null;
4777: end if;
4778: if g_debug then
4779: write_to_log_file_n('Going to execute '||l_stmt||get_time);
4781: execute immediate l_stmt;
4782: if g_debug then
4783: write_to_log_file_n('created '||l_ilog||' with '||sql%rowcount||' rows '||get_time);
4784: end if;
4785: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_ilog,instr(l_ilog,'.')+1,length(l_ilog)),
4786: substr(l_ilog,1,instr(l_ilog,'.')-1));
4787: if g_ll_snplog_has_pk then
4788: l_stmt:='insert into '||g_ilog||'(row_id,'||g_ltc_pk||',status) select row_id,'||g_ltc_pk||
4789: ',status from '||l_ilog;
4792: end if;
4793: if g_debug then
4794: write_to_log_file_n('Going to execute '||l_stmt||get_time);
4795: end if;
4796: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
4797: execute immediate l_stmt;
4798: if g_debug then
4799: write_to_log_file_n('Inserted '||sql%rowcount||' rows into '||g_ilog||get_time);
4800: end if;
4799: write_to_log_file_n('Inserted '||sql%rowcount||' rows into '||g_ilog||get_time);
4800: end if;
4801: commit;
4802: if l_ilog is not null then
4803: if EDW_OWB_COLLECTION_UTIL.drop_table(l_ilog)=false then
4804: null;
4805: end if;
4806: end if;
4807: end if;
4804: null;
4805: end if;
4806: end if;
4807: end if;
4808: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_ilog,instr(g_ilog,'.')+1,length(g_ilog)),
4809: substr(g_ilog,1,instr(g_ilog,'.')-1));
4810: return true;
4811: Exception when others then
4812: g_status_message:=sqlerrm;
4824: Begin
4825: if g_debug then
4826: write_to_log_file_n('In create_L_from_ilog');
4827: end if;
4828: if EDW_OWB_COLLECTION_UTIL.drop_table(g_snplogs_L(g_lowest_level_index))=false then
4829: null;
4830: end if;
4831: l_stmt:='create table '||g_snplogs_L(g_lowest_level_index)||' tablespace '||g_op_table_space;
4832: if g_parallel is not null then
4841: write_to_log_file_n('Created '||g_snplogs_L(g_lowest_level_index)||'with '||sql%rowcount||
4842: ' rows '||get_time);
4843: end if;
4844: commit;
4845: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_snplogs_L(g_lowest_level_index),
4846: instr(g_snplogs_L(g_lowest_level_index),'.')+1,length(g_snplogs_L(g_lowest_level_index))),
4847: substr(g_snplogs_L(g_lowest_level_index),1,instr(g_snplogs_L(g_lowest_level_index),'.')-1));
4848: return true;
4849: Exception when others then
4864: if g_debug then
4865: write_to_log_file_n('In name_op_tables');
4866: end if;
4867: for i in 1..g_number_levels loop
4868: l_prefix:=EDW_OWB_COLLECTION_UTIL.get_level_prefix(substr(g_levels(i),1,instr(g_levels(i),'_LTC')-1));
4869: l_name_org:='TAB_'||g_dim_id||'_'||l_prefix||'_';
4870: if p_job_id is null then
4871: l_name:='TAB_'||g_dim_id||'_'||l_prefix||'_';
4872: else
4892: function create_ltc_copies(p_mode varchar2) return boolean is
4893: l_stmt varchar2(10000);
4894: l_pk varchar2(400);
4895: l_consider boolean;
4896: l_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
4897: l_number_cols number;
4898: l_L_count number;
4899: l_nl_flag boolean;
4900: Begin
4903: end if;
4904: for i in 1..g_number_levels loop
4905: l_consider:=false;
4906: if p_mode='SNP' then
4907: if EDW_OWB_COLLECTION_UTIL.drop_table(g_levels_copy(i))=false then
4908: null;
4909: end if;
4910: if g_consider_level(i) then
4911: l_consider:=true;
4910: if g_consider_level(i) then
4911: l_consider:=true;
4912: end if;
4913: elsif p_mode='NON-SNP' and g_consider_level(i)=false then
4914: if EDW_OWB_COLLECTION_UTIL.drop_table(g_levels_copy(i))=false then
4915: null;
4916: end if;
4917: l_consider:=true;
4918: end if;
4916: end if;
4917: l_consider:=true;
4918: end if;
4919: if l_consider then
4920: if EDW_OWB_COLLECTION_UTIL.check_table(g_snplogs_L(i))=false then
4921: l_stmt:='create table '||g_snplogs_L(i)||' tablespace '||g_op_table_space;
4922: l_stmt:=l_stmt||' as select rowid row_id from '||g_levels(i)||' where 1=2';
4923: if g_debug then
4924: write_to_log_file_n('Going to execute '||l_stmt||get_time);
4928: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
4929: end if;
4930: end if;
4931: if g_level_count(i) is null then
4932: g_level_count(i):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(i),g_table_owner);
4933: end if;
4934: l_L_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_snplogs_L(i),g_bis_owner);
4935: l_nl_flag:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_L_count,g_level_count(i),g_join_nl_percentage);
4936: l_stmt:='create table '||g_levels_copy(i)||' tablespace '||g_op_table_space;
4930: end if;
4931: if g_level_count(i) is null then
4932: g_level_count(i):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(i),g_table_owner);
4933: end if;
4934: l_L_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_snplogs_L(i),g_bis_owner);
4935: l_nl_flag:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_L_count,g_level_count(i),g_join_nl_percentage);
4936: l_stmt:='create table '||g_levels_copy(i)||' tablespace '||g_op_table_space;
4937: if g_parallel is not null then
4938: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
4931: if g_level_count(i) is null then
4932: g_level_count(i):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(i),g_table_owner);
4933: end if;
4934: l_L_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_snplogs_L(i),g_bis_owner);
4935: l_nl_flag:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_L_count,g_level_count(i),g_join_nl_percentage);
4936: l_stmt:='create table '||g_levels_copy(i)||' tablespace '||g_op_table_space;
4937: if g_parallel is not null then
4938: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
4939: end if;
4957: end loop;
4958: if g_number_src_fk_table>0 then
4959: for j in 1..g_number_src_fk_table loop
4960: if g_src_fk_table(j)=g_levels(i) then
4961: if EDW_OWB_COLLECTION_UTIL.value_in_table(l_cols,l_number_cols,g_src_fk(j))=false then
4962: l_number_cols:=l_number_cols+1;
4963: l_cols(l_number_cols):=g_src_fk(j);
4964: end if;
4965: end if;
4970: --higher levels
4971: if g_levels(i)<>g_lowest_level_global then
4972: for j in 1..g_fk_pk_number loop
4973: if g_fk_pk_parent_level(j)=g_levels(i) then
4974: if EDW_OWB_COLLECTION_UTIL.value_in_table(l_cols,l_number_cols,g_fk_pk_parent_pk(j))=false then
4975: l_number_cols:=l_number_cols+1;
4976: l_cols(l_number_cols):=g_fk_pk_parent_pk(j);
4977: end if;
4978: end if;
5028: end if;
5029: end if;
5030: end loop;
5031: end if;
5032: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_levels_copy(i),instr(g_levels_copy(i),'.')+1,
5033: length(g_levels_copy(i))),substr(g_levels_copy(i),1,instr(g_levels_copy(i),'.')-1));
5034: end if;
5035: end loop;
5036: return true;
5047: if g_debug then
5048: write_to_log_file_n('In drop_L_tables');
5049: end if;
5050: for i in 1..g_number_levels loop
5051: if EDW_OWB_COLLECTION_UTIL.drop_table(g_snplogs_L(i))=false then
5052: null;
5053: end if;
5054: end loop;
5055: return true;
5066: write_to_log_file_n('In drop_I_tables');
5067: end if;
5068: for i in 1..g_number_levels loop
5069: if g_levels_I(i)<>g_levels(i) then
5070: if EDW_OWB_COLLECTION_UTIL.drop_table(g_levels_I(i))=false then
5071: null;
5072: end if;
5073: end if;
5074: end loop;
5086: if g_debug then
5087: write_to_log_file_n('In drop_ltc_copies');
5088: end if;
5089: for i in 1..g_number_levels loop
5090: if EDW_OWB_COLLECTION_UTIL.drop_table(g_levels_copy(i))=false then
5091: null;
5092: end if;
5093: end loop;
5094: if EDW_OWB_COLLECTION_UTIL.drop_table(g_levels_copy_low_hd_ins)=false then
5090: if EDW_OWB_COLLECTION_UTIL.drop_table(g_levels_copy(i))=false then
5091: null;
5092: end if;
5093: end loop;
5094: if EDW_OWB_COLLECTION_UTIL.drop_table(g_levels_copy_low_hd_ins)=false then
5095: null;
5096: end if;
5097: return true;
5098: Exception when others then
5116: --case 3: there is change to any levels
5117: g_number_considered_parent:=0;
5118: if g_where_snplog_stmt is not null and g_number_levels>1 then
5119: for i in 1..g_number_levels loop
5120: l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,g_level_order(i));
5121: if g_consider_level(l_index) then
5122: if g_debug then
5123: write_to_log_file_n('Level '||g_level_order(i)||' has changed. Drill down net change');
5124: end if;
5166: end if;
5167: end if;
5168: if g_number_levels>1 then
5169: for i in 1..g_number_levels loop
5170: l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,
5171: g_level_order(g_number_levels+1-i));
5172: if p_mode='SNP' and g_consider_level(l_index) then
5173: if find_rowid_child_change(g_level_order(g_number_levels+1-i),p_mode)=false then
5174: return false;
5236: g_from_stmt_hd_row:=substr(g_from_stmt_hd_row,1,length(g_from_stmt_hd_row)-1);--for hold nested loop
5237: /*
5238: never must g_levels be changed!!
5239: */
5240: l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_levels,g_number_levels,g_lowest_level);
5241: if l_index>0 then
5242: g_lowest_level:=g_levels_copy(l_index);
5243: end if;
5244: if g_debug then
5273: write_to_log_file_n('In insert_into_ilog');
5274: end if;
5275: if g_dim_empty_flag then
5276: --create the ilog
5277: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog) = false then
5278: write_to_log_file_n(g_ilog||' table not found for dropping');
5279: end if;
5280: --if create_ilog_table= false then
5281: --write_to_log_file_n('create_ilog_table returned with false');
5348: end if;
5349: if g_debug then
5350: write_to_log_file_n('Going to execute '||l_stmt);
5351: end if;
5352: --EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
5353: execute immediate l_stmt;
5354: if g_debug then
5355: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
5356: end if;
5355: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
5356: end if;
5357: commit;
5358: if p_multi_thread then
5359: edw_owb_collection_util.create_rownum_index_ilog(g_ilog,g_op_table_space,g_parallel);
5360: end if;
5361: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_ilog,instr(g_ilog,'.')+1,length(g_ilog)),
5362: substr(g_ilog,1,instr(g_ilog,'.')-1));
5363: exception when others then
5357: commit;
5358: if p_multi_thread then
5359: edw_owb_collection_util.create_rownum_index_ilog(g_ilog,g_op_table_space,g_parallel);
5360: end if;
5361: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_ilog,instr(g_ilog,'.')+1,length(g_ilog)),
5362: substr(g_ilog,1,instr(g_ilog,'.')-1));
5363: exception when others then
5364: g_status_message:=sqlerrm;
5365: write_to_log_file_n(g_status_message);
5488: procedure analyze_snplogs is
5489: Begin
5490: for i in 1..g_number_levels loop
5491: if g_consider_snapshot(i) then
5492: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(g_level_snapshot_logs(i),g_table_owner);
5493: end if;
5494: end loop;
5495: Exception when others then
5496: g_status:=false;
5503: if g_debug then
5504: write_to_log_file_n('In get_snapshot_log');
5505: end if;
5506: for i in 1..g_number_levels loop
5507: --g_level_snapshot_logs(i):=EDW_OWB_COLLECTION_UTIL.get_table_snapshot_log(g_levels(i));
5508: g_consider_snapshot(i):=false;
5509: end loop;
5510: return true;
5511: Exception when others then
5521: begin
5522: if g_debug then
5523: write_to_log_file_n('In check_snapshot_logs'||get_time);
5524: end if;
5525: if EDW_OWB_COLLECTION_UTIL.check_table(g_ilog) then
5526: g_error_rec_flag:=true;
5527: elsif EDW_OWB_COLLECTION_UTIL.check_table(g_ilog||'A')=true then
5528: g_ilog:=g_ilog||'A';
5529: g_error_rec_flag:=true;
5523: write_to_log_file_n('In check_snapshot_logs'||get_time);
5524: end if;
5525: if EDW_OWB_COLLECTION_UTIL.check_table(g_ilog) then
5526: g_error_rec_flag:=true;
5527: elsif EDW_OWB_COLLECTION_UTIL.check_table(g_ilog||'A')=true then
5528: g_ilog:=g_ilog||'A';
5529: g_error_rec_flag:=true;
5530: end if;
5531: if g_fresh_restart then
5528: g_ilog:=g_ilog||'A';
5529: g_error_rec_flag:=true;
5530: end if;
5531: if g_fresh_restart then
5532: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
5533: null;
5534: end if;
5535: g_error_rec_flag:=false;
5536: end if;
5535: g_error_rec_flag:=false;
5536: end if;
5537: l_consider:=false;
5538: for i in 1..g_number_levels loop
5539: v_res:=EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_level_snapshot_logs(i));
5540: if v_res=0 then
5541: if g_debug then
5542: write_to_log_file('Snapshot NOT FOUND!');
5543: end if;
5556: end if;
5557: end loop;
5558: if l_consider=false then
5559: --check ilog also
5560: v_res:=EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_ilog);
5561: if v_res=2 then
5562: l_consider:=true;
5563: end if;
5564: end if;
5580: if g_exec_flag = true then
5581: if g_debug then
5582: write_to_log_file_n('Going to execute g_temp_insert_sql, inserting into table '||g_dim_name_temp_int);
5583: end if;
5584: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
5585: execute immediate g_temp_insert_stmt;
5586: l_count:=sql%rowcount;
5587: commit;
5588: if g_debug then
5593: write_to_log_file_n('commit');
5594: end if;
5595: --analyze the table
5596: if sql%rowcount > 0 then
5597: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_dim_name_temp_int,instr(g_dim_name_temp_int,'.')+1,
5598: length(g_dim_name_temp_int)),substr(g_dim_name_temp_int,1,instr(g_dim_name_temp_int,'.')-1));
5599: end if;
5600: commit;
5601: if g_debug then
5619: write_to_log_file_n('In truncate_ltc_snapshot_logs'||get_time);
5620: end if;
5621:
5622: for i in 1..g_number_levels loop
5623: if EDW_OWB_COLLECTION_UTIL.truncate_table(g_level_snapshot_logs(i),g_table_owner)=false then
5624: if g_debug then
5625: write_to_log_file_n(g_table_owner||' is not the owner for '||g_level_snapshot_logs(i));
5626: end if;
5627: l_owner:=null;
5624: if g_debug then
5625: write_to_log_file_n(g_table_owner||' is not the owner for '||g_level_snapshot_logs(i));
5626: end if;
5627: l_owner:=null;
5628: l_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(g_level_snapshot_logs(i));
5629: if g_debug then
5630: write_to_log_file_n('Owner is '||l_owner);
5631: end if;
5632: if l_owner is not null then
5629: if g_debug then
5630: write_to_log_file_n('Owner is '||l_owner);
5631: end if;
5632: if l_owner is not null then
5633: if EDW_OWB_COLLECTION_UTIL.truncate_table(g_level_snapshot_logs(i),l_owner)=false then
5634: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
5635: write_to_log_file_n(g_status_message);
5636: g_status:=false;
5637: return;
5630: write_to_log_file_n('Owner is '||l_owner);
5631: end if;
5632: if l_owner is not null then
5633: if EDW_OWB_COLLECTION_UTIL.truncate_table(g_level_snapshot_logs(i),l_owner)=false then
5634: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
5635: write_to_log_file_n(g_status_message);
5636: g_status:=false;
5637: return;
5638: end if;
5636: g_status:=false;
5637: return;
5638: end if;
5639: else
5640: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
5641: write_to_log_file_n(g_status_message);
5642: g_status:=false;
5643: return;
5644: end if;
5652: End;--PROCEDURE truncate_ltc_snapshot_logs IS
5653:
5654: procedure clean_up is
5655: Begin
5656: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_temp)=false then
5657: null;
5658: end if;
5659: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_temp_int)=false then
5660: null;
5655: Begin
5656: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_temp)=false then
5657: null;
5658: end if;
5659: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_temp_int)=false then
5660: null;
5661: end if;
5662: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
5663: null;
5658: end if;
5659: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_temp_int)=false then
5660: null;
5661: end if;
5662: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
5663: null;
5664: end if;
5665: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_small)=false then
5666: null;
5661: end if;
5662: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
5663: null;
5664: end if;
5665: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_small)=false then
5666: null;
5667: end if;
5668: if g_ilog_prev is not null then
5669: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_prev)=false then
5665: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_small)=false then
5666: null;
5667: end if;
5668: if g_ilog_prev is not null then
5669: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_prev)=false then
5670: null;
5671: end if;
5672: end if;
5673: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_hold)=false then
5669: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_prev)=false then
5670: null;
5671: end if;
5672: end if;
5673: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_hold)=false then
5674: null;
5675: end if;
5676: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_kl_table)=false then
5677: null;
5672: end if;
5673: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_hold)=false then
5674: null;
5675: end if;
5676: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_kl_table)=false then
5677: null;
5678: end if;
5679: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_with_slow)=false then
5680: null;
5675: end if;
5676: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_kl_table)=false then
5677: null;
5678: end if;
5679: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_with_slow)=false then
5680: null;
5681: end if;
5682: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_kl_table_temp)=false then
5683: null;
5678: end if;
5679: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_with_slow)=false then
5680: null;
5681: end if;
5682: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_kl_table_temp)=false then
5683: null;
5684: end if;
5685: if drop_ltc_copies=false then
5686: null;
5688: if drop_I_tables=false then
5689: null;
5690: end if;
5691: for i in 1..g_number_objects_to_drop loop
5692: if EDW_OWB_COLLECTION_UTIL.drop_table(g_objects_to_drop(i))=false then
5693: null;
5694: end if;
5695: end loop;
5696: Exception when others then
5703: called by the jobs. global tables are not dropped
5704: */
5705: procedure clean_up_job is
5706: Begin
5707: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_temp)=false then
5708: null;
5709: end if;
5710: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_temp_int)=false then
5711: null;
5706: Begin
5707: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_temp)=false then
5708: null;
5709: end if;
5710: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_temp_int)=false then
5711: null;
5712: end if;
5713: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
5714: null;
5709: end if;
5710: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_temp_int)=false then
5711: null;
5712: end if;
5713: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
5714: null;
5715: end if;
5716: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_small)=false then
5717: null;
5712: end if;
5713: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
5714: null;
5715: end if;
5716: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_small)=false then
5717: null;
5718: end if;
5719: if g_ilog_prev is not null then
5720: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_prev)=false then
5716: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_small)=false then
5717: null;
5718: end if;
5719: if g_ilog_prev is not null then
5720: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_prev)=false then
5721: null;
5722: end if;
5723: end if;
5724: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_hold)=false then
5720: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_prev)=false then
5721: null;
5722: end if;
5723: end if;
5724: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_hold)=false then
5725: null;
5726: end if;
5727: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_kl_table)=false then
5728: null;
5723: end if;
5724: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_name_hold)=false then
5725: null;
5726: end if;
5727: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_kl_table)=false then
5728: null;
5729: end if;
5730: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_kl_table_temp)=false then
5731: null;
5726: end if;
5727: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_kl_table)=false then
5728: null;
5729: end if;
5730: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dim_kl_table_temp)=false then
5731: null;
5732: end if;
5733: if drop_ltc_copies=false then
5734: null;
5733: if drop_ltc_copies=false then
5734: null;
5735: end if;
5736: for i in 1..g_number_objects_to_drop loop
5737: if EDW_OWB_COLLECTION_UTIL.drop_table(g_objects_to_drop(i))=false then
5738: null;
5739: end if;
5740: end loop;
5741: Exception when others then
5854: End;
5855:
5856: procedure write_to_log_file(p_message varchar2) is
5857: begin
5858: EDW_OWB_COLLECTION_UTIL.write_to_log_file(p_message);
5859: Exception when others then
5860: null;
5861: End;
5862:
5895:
5896: procedure insert_into_load_progress(p_load_fk number,p_object_name varchar2,p_load_progress varchar2,
5897: p_start_date date,p_end_date date,p_category varchar2, p_operation varchar2,p_seq_id varchar2,p_flag varchar2) is
5898: Begin
5899: EDW_OWB_COLLECTION_UTIL.insert_into_load_progress(p_load_fk,p_object_name,g_dim_id,p_load_progress,p_start_date,
5900: p_end_date,p_category,p_operation,p_seq_id,p_flag,1000);
5901: commit;
5902: Exception when others then
5903: g_status_message:=sqlerrm;
5908: procedure insert_into_load_progress_d(p_load_fk number,p_object_name varchar2,p_load_progress varchar2,
5909: p_start_date date,p_end_date date,p_category varchar2, p_operation varchar2,p_seq_id varchar2,p_flag varchar2) is
5910: Begin
5911: if g_debug then
5912: EDW_OWB_COLLECTION_UTIL.insert_into_load_progress(p_load_fk,p_object_name,g_dim_id,p_load_progress,p_start_date,
5913: p_end_date,p_category,p_operation,p_seq_id,p_flag,1000);
5914: commit;
5915: end if;
5916: Exception when others then
5923: l_analyze boolean:=false;
5924: l_diff number;
5925: Begin
5926: if g_before_update_table_final is not null then
5927: if EDW_OWB_COLLECTION_UTIL.check_table(g_before_update_table_final) then
5928: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_before_update_table_final,
5929: instr(g_before_update_table_final,'.')+1,length(g_before_update_table_final)),
5930: substr(g_before_update_table_final,1,instr(g_before_update_table_final,'.')-1),1);
5931: end if;
5924: l_diff number;
5925: Begin
5926: if g_before_update_table_final is not null then
5927: if EDW_OWB_COLLECTION_UTIL.check_table(g_before_update_table_final) then
5928: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_before_update_table_final,
5929: instr(g_before_update_table_final,'.')+1,length(g_before_update_table_final)),
5930: substr(g_before_update_table_final,1,instr(g_before_update_table_final,'.')-1),1);
5931: end if;
5932: end if;
5929: instr(g_before_update_table_final,'.')+1,length(g_before_update_table_final)),
5930: substr(g_before_update_table_final,1,instr(g_before_update_table_final,'.')-1),1);
5931: end if;
5932: end if;
5933: l_date:=EDW_OWB_COLLECTION_UTIL.last_analyzed_date(g_dim_name,g_table_owner);
5934: if g_debug then
5935: write_to_log_file_n('Last analyzed date for '||g_dim_name||' '||
5936: to_char(l_date,'MM-DD-YYYY HH24:MI:SS'));
5937: end if;
5937: end if;
5938: if l_date is null or (sysdate-l_date)>=g_analyze_freq then
5939: insert_into_load_progress_d(g_load_pk,g_dim_name,'Analyze Star Table'||g_jobid_stmt,sysdate,null,'DIMENSION',
5940: 'ANALYZE','AN2200'||g_job_id,'I');
5941: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(g_dim_name,g_table_owner,1);
5942: if g_debug then
5943: write_to_log_file_n('Table '||g_dim_name||' analyzed');
5944: end if;
5945: commit;
5951: End;
5952:
5953: function get_dim_storage return boolean is
5954: Begin
5955: if EDW_OWB_COLLECTION_UTIL.get_table_next_extent(g_dim_name,g_table_owner,g_dim_next_extent)=false then
5956: return false;
5957: end if;
5958: if g_dim_next_extent is null or g_dim_next_extent=0 then
5959: g_dim_next_extent:=16777216;
5964: return false;
5965: End;
5966:
5967: function is_dim_in_derv_map return boolean is
5968: l_dim_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
5969: l_number_dim_fk number;
5970: Begin
5971: g_derv_snp_change_flag:=false;
5972: if EDW_OWB_COLLECTION_UTIL.get_mapid_dim_in_derv_map(g_dim_name,g_dim_derv_map_id,
5968: l_dim_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
5969: l_number_dim_fk number;
5970: Begin
5971: g_derv_snp_change_flag:=false;
5972: if EDW_OWB_COLLECTION_UTIL.get_mapid_dim_in_derv_map(g_dim_name,g_dim_derv_map_id,
5973: g_derv_fact_id,g_src_fact_id,g_number_dim_derv_map_id,'INC')=false then
5974: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
5975: return false;
5976: end if;
5970: Begin
5971: g_derv_snp_change_flag:=false;
5972: if EDW_OWB_COLLECTION_UTIL.get_mapid_dim_in_derv_map(g_dim_name,g_dim_derv_map_id,
5973: g_derv_fact_id,g_src_fact_id,g_number_dim_derv_map_id,'INC')=false then
5974: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
5975: return false;
5976: end if;
5977: for i in 1..g_number_dim_derv_map_id loop
5978: g_dim_derv_map_refresh(i):=true;--by default assume that all maps need to be refreshed
5991: g_number_dim_derv_pk_key:=0;
5992: g_num_dim_derv_col_map_all:=0;
5993: for i in 1..g_number_dim_derv_map_id loop
5994: l_number_dim_fk:=0;
5995: if EDW_OWB_COLLECTION_UTIL.get_dim_fk_summary_fact(g_derv_fact_id(i),g_dim_id,l_dim_fk,l_number_dim_fk)=false then
5996: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
5997: return false;
5998: end if;
5999: for j in 1..l_number_dim_fk loop
5992: g_num_dim_derv_col_map_all:=0;
5993: for i in 1..g_number_dim_derv_map_id loop
5994: l_number_dim_fk:=0;
5995: if EDW_OWB_COLLECTION_UTIL.get_dim_fk_summary_fact(g_derv_fact_id(i),g_dim_id,l_dim_fk,l_number_dim_fk)=false then
5996: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
5997: return false;
5998: end if;
5999: for j in 1..l_number_dim_fk loop
6000: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_dim_derv_pk_key,g_number_dim_derv_pk_key,l_dim_fk(j))=false then
5996: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
5997: return false;
5998: end if;
5999: for j in 1..l_number_dim_fk loop
6000: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_dim_derv_pk_key,g_number_dim_derv_pk_key,l_dim_fk(j))=false then
6001: g_number_dim_derv_pk_key:=g_number_dim_derv_pk_key+1;
6002: g_dim_derv_pk_key(g_number_dim_derv_pk_key):=l_dim_fk(j);
6003: end if;
6004: end loop;
6045: end if;
6046: end if;
6047: if g_derv_snp_change_flag then
6048: for i in 1..g_number_dim_derv_map_id loop
6049: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_dim_derv_col_map_all,g_num_dim_derv_col_map_all,
6050: g_dim_derv_map_id(i))=false then
6051: g_dim_derv_map_refresh(i):=false;
6052: end if;
6053: end loop;
6082: return false;
6083: End;
6084:
6085: function get_derv_fact_map_details(p_mapping_id number) return boolean is
6086: l_hold_func EDW_OWB_COLLECTION_UTIL.varcharTableType;
6087: l_hold_func_category EDW_OWB_COLLECTION_UTIL.varcharTableType;
6088: l_hold_item EDW_OWB_COLLECTION_UTIL.varcharTableType;
6089: l_hold_item_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6090: l_hold_item_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6083: End;
6084:
6085: function get_derv_fact_map_details(p_mapping_id number) return boolean is
6086: l_hold_func EDW_OWB_COLLECTION_UTIL.varcharTableType;
6087: l_hold_func_category EDW_OWB_COLLECTION_UTIL.varcharTableType;
6088: l_hold_item EDW_OWB_COLLECTION_UTIL.varcharTableType;
6089: l_hold_item_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6090: l_hold_item_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6091: l_hold_aggregatefunction EDW_OWB_COLLECTION_UTIL.varcharTableType;
6084:
6085: function get_derv_fact_map_details(p_mapping_id number) return boolean is
6086: l_hold_func EDW_OWB_COLLECTION_UTIL.varcharTableType;
6087: l_hold_func_category EDW_OWB_COLLECTION_UTIL.varcharTableType;
6088: l_hold_item EDW_OWB_COLLECTION_UTIL.varcharTableType;
6089: l_hold_item_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6090: l_hold_item_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6091: l_hold_aggregatefunction EDW_OWB_COLLECTION_UTIL.varcharTableType;
6092: l_hold_is_distinct EDW_OWB_COLLECTION_UTIL.numberTableType;
6085: function get_derv_fact_map_details(p_mapping_id number) return boolean is
6086: l_hold_func EDW_OWB_COLLECTION_UTIL.varcharTableType;
6087: l_hold_func_category EDW_OWB_COLLECTION_UTIL.varcharTableType;
6088: l_hold_item EDW_OWB_COLLECTION_UTIL.varcharTableType;
6089: l_hold_item_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6090: l_hold_item_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6091: l_hold_aggregatefunction EDW_OWB_COLLECTION_UTIL.varcharTableType;
6092: l_hold_is_distinct EDW_OWB_COLLECTION_UTIL.numberTableType;
6093: l_hold_relation EDW_OWB_COLLECTION_UTIL.numberTableType;
6086: l_hold_func EDW_OWB_COLLECTION_UTIL.varcharTableType;
6087: l_hold_func_category EDW_OWB_COLLECTION_UTIL.varcharTableType;
6088: l_hold_item EDW_OWB_COLLECTION_UTIL.varcharTableType;
6089: l_hold_item_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6090: l_hold_item_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6091: l_hold_aggregatefunction EDW_OWB_COLLECTION_UTIL.varcharTableType;
6092: l_hold_is_distinct EDW_OWB_COLLECTION_UTIL.numberTableType;
6093: l_hold_relation EDW_OWB_COLLECTION_UTIL.numberTableType;
6094: l_hold_relation_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
6087: l_hold_func_category EDW_OWB_COLLECTION_UTIL.varcharTableType;
6088: l_hold_item EDW_OWB_COLLECTION_UTIL.varcharTableType;
6089: l_hold_item_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6090: l_hold_item_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6091: l_hold_aggregatefunction EDW_OWB_COLLECTION_UTIL.varcharTableType;
6092: l_hold_is_distinct EDW_OWB_COLLECTION_UTIL.numberTableType;
6093: l_hold_relation EDW_OWB_COLLECTION_UTIL.numberTableType;
6094: l_hold_relation_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
6095: l_hold_relation_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6088: l_hold_item EDW_OWB_COLLECTION_UTIL.varcharTableType;
6089: l_hold_item_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6090: l_hold_item_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6091: l_hold_aggregatefunction EDW_OWB_COLLECTION_UTIL.varcharTableType;
6092: l_hold_is_distinct EDW_OWB_COLLECTION_UTIL.numberTableType;
6093: l_hold_relation EDW_OWB_COLLECTION_UTIL.numberTableType;
6094: l_hold_relation_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
6095: l_hold_relation_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6096: l_hold_relation_type EDW_OWB_COLLECTION_UTIL.varcharTableType;
6089: l_hold_item_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6090: l_hold_item_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6091: l_hold_aggregatefunction EDW_OWB_COLLECTION_UTIL.varcharTableType;
6092: l_hold_is_distinct EDW_OWB_COLLECTION_UTIL.numberTableType;
6093: l_hold_relation EDW_OWB_COLLECTION_UTIL.numberTableType;
6094: l_hold_relation_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
6095: l_hold_relation_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6096: l_hold_relation_type EDW_OWB_COLLECTION_UTIL.varcharTableType;
6097: l_hold_func_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6090: l_hold_item_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6091: l_hold_aggregatefunction EDW_OWB_COLLECTION_UTIL.varcharTableType;
6092: l_hold_is_distinct EDW_OWB_COLLECTION_UTIL.numberTableType;
6093: l_hold_relation EDW_OWB_COLLECTION_UTIL.numberTableType;
6094: l_hold_relation_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
6095: l_hold_relation_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6096: l_hold_relation_type EDW_OWB_COLLECTION_UTIL.varcharTableType;
6097: l_hold_func_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6098: l_hold_func_position EDW_OWB_COLLECTION_UTIL.numberTableType;
6091: l_hold_aggregatefunction EDW_OWB_COLLECTION_UTIL.varcharTableType;
6092: l_hold_is_distinct EDW_OWB_COLLECTION_UTIL.numberTableType;
6093: l_hold_relation EDW_OWB_COLLECTION_UTIL.numberTableType;
6094: l_hold_relation_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
6095: l_hold_relation_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6096: l_hold_relation_type EDW_OWB_COLLECTION_UTIL.varcharTableType;
6097: l_hold_func_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6098: l_hold_func_position EDW_OWB_COLLECTION_UTIL.numberTableType;
6099: l_hold_func_dvalue EDW_OWB_COLLECTION_UTIL.varcharTableType;
6092: l_hold_is_distinct EDW_OWB_COLLECTION_UTIL.numberTableType;
6093: l_hold_relation EDW_OWB_COLLECTION_UTIL.numberTableType;
6094: l_hold_relation_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
6095: l_hold_relation_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6096: l_hold_relation_type EDW_OWB_COLLECTION_UTIL.varcharTableType;
6097: l_hold_func_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6098: l_hold_func_position EDW_OWB_COLLECTION_UTIL.numberTableType;
6099: l_hold_func_dvalue EDW_OWB_COLLECTION_UTIL.varcharTableType;
6100: l_hold_number number;
6093: l_hold_relation EDW_OWB_COLLECTION_UTIL.numberTableType;
6094: l_hold_relation_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
6095: l_hold_relation_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6096: l_hold_relation_type EDW_OWB_COLLECTION_UTIL.varcharTableType;
6097: l_hold_func_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6098: l_hold_func_position EDW_OWB_COLLECTION_UTIL.numberTableType;
6099: l_hold_func_dvalue EDW_OWB_COLLECTION_UTIL.varcharTableType;
6100: l_hold_number number;
6101: l_stmt varchar2(2000);
6094: l_hold_relation_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
6095: l_hold_relation_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6096: l_hold_relation_type EDW_OWB_COLLECTION_UTIL.varcharTableType;
6097: l_hold_func_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6098: l_hold_func_position EDW_OWB_COLLECTION_UTIL.numberTableType;
6099: l_hold_func_dvalue EDW_OWB_COLLECTION_UTIL.varcharTableType;
6100: l_hold_number number;
6101: l_stmt varchar2(2000);
6102: l_filter_stmt varchar2(20000);
6095: l_hold_relation_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6096: l_hold_relation_type EDW_OWB_COLLECTION_UTIL.varcharTableType;
6097: l_hold_func_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6098: l_hold_func_position EDW_OWB_COLLECTION_UTIL.numberTableType;
6099: l_hold_func_dvalue EDW_OWB_COLLECTION_UTIL.varcharTableType;
6100: l_hold_number number;
6101: l_stmt varchar2(2000);
6102: l_filter_stmt varchar2(20000);
6103: l_sec_source EDW_OWB_COLLECTION_UTIL.varcharTableType;
6099: l_hold_func_dvalue EDW_OWB_COLLECTION_UTIL.varcharTableType;
6100: l_hold_number number;
6101: l_stmt varchar2(2000);
6102: l_filter_stmt varchar2(20000);
6103: l_sec_source EDW_OWB_COLLECTION_UTIL.varcharTableType;
6104: l_sec_source_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6105: l_sec_source_child EDW_OWB_COLLECTION_UTIL.varcharTableType;
6106: l_sec_source_child_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6107: l_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6100: l_hold_number number;
6101: l_stmt varchar2(2000);
6102: l_filter_stmt varchar2(20000);
6103: l_sec_source EDW_OWB_COLLECTION_UTIL.varcharTableType;
6104: l_sec_source_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6105: l_sec_source_child EDW_OWB_COLLECTION_UTIL.varcharTableType;
6106: l_sec_source_child_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6107: l_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6108: l_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6101: l_stmt varchar2(2000);
6102: l_filter_stmt varchar2(20000);
6103: l_sec_source EDW_OWB_COLLECTION_UTIL.varcharTableType;
6104: l_sec_source_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6105: l_sec_source_child EDW_OWB_COLLECTION_UTIL.varcharTableType;
6106: l_sec_source_child_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6107: l_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6108: l_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6109: l_sec_source_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6102: l_filter_stmt varchar2(20000);
6103: l_sec_source EDW_OWB_COLLECTION_UTIL.varcharTableType;
6104: l_sec_source_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6105: l_sec_source_child EDW_OWB_COLLECTION_UTIL.varcharTableType;
6106: l_sec_source_child_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6107: l_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6108: l_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6109: l_sec_source_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6110: l_sec_source_usage_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
6103: l_sec_source EDW_OWB_COLLECTION_UTIL.varcharTableType;
6104: l_sec_source_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6105: l_sec_source_child EDW_OWB_COLLECTION_UTIL.varcharTableType;
6106: l_sec_source_child_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6107: l_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6108: l_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6109: l_sec_source_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6110: l_sec_source_usage_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
6111: l_sec_source_child_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6104: l_sec_source_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6105: l_sec_source_child EDW_OWB_COLLECTION_UTIL.varcharTableType;
6106: l_sec_source_child_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6107: l_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6108: l_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6109: l_sec_source_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6110: l_sec_source_usage_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
6111: l_sec_source_child_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6112: l_sec_source_number number;
6105: l_sec_source_child EDW_OWB_COLLECTION_UTIL.varcharTableType;
6106: l_sec_source_child_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6107: l_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6108: l_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6109: l_sec_source_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6110: l_sec_source_usage_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
6111: l_sec_source_child_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6112: l_sec_source_number number;
6113: l_sec_sources_child_alias EDW_OWB_COLLECTION_UTIL.varcharTableType;
6106: l_sec_source_child_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6107: l_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6108: l_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6109: l_sec_source_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6110: l_sec_source_usage_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
6111: l_sec_source_child_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6112: l_sec_source_number number;
6113: l_sec_sources_child_alias EDW_OWB_COLLECTION_UTIL.varcharTableType;
6114: l_sec_source_name_index EDW_OWB_COLLECTION_UTIL.varcharTableType;
6107: l_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6108: l_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6109: l_sec_source_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6110: l_sec_source_usage_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
6111: l_sec_source_child_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6112: l_sec_source_number number;
6113: l_sec_sources_child_alias EDW_OWB_COLLECTION_UTIL.varcharTableType;
6114: l_sec_source_name_index EDW_OWB_COLLECTION_UTIL.varcharTableType;
6115: l_sec_source_index EDW_OWB_COLLECTION_UTIL.numberTableType;
6109: l_sec_source_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6110: l_sec_source_usage_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
6111: l_sec_source_child_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6112: l_sec_source_number number;
6113: l_sec_sources_child_alias EDW_OWB_COLLECTION_UTIL.varcharTableType;
6114: l_sec_source_name_index EDW_OWB_COLLECTION_UTIL.varcharTableType;
6115: l_sec_source_index EDW_OWB_COLLECTION_UTIL.numberTableType;
6116: l_sec_source_index_number number;
6117: l_start number;
6110: l_sec_source_usage_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
6111: l_sec_source_child_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6112: l_sec_source_number number;
6113: l_sec_sources_child_alias EDW_OWB_COLLECTION_UTIL.varcharTableType;
6114: l_sec_source_name_index EDW_OWB_COLLECTION_UTIL.varcharTableType;
6115: l_sec_source_index EDW_OWB_COLLECTION_UTIL.numberTableType;
6116: l_sec_source_index_number number;
6117: l_start number;
6118: l_end number;
6111: l_sec_source_child_usage EDW_OWB_COLLECTION_UTIL.numberTableType;
6112: l_sec_source_number number;
6113: l_sec_sources_child_alias EDW_OWB_COLLECTION_UTIL.varcharTableType;
6114: l_sec_source_name_index EDW_OWB_COLLECTION_UTIL.varcharTableType;
6115: l_sec_source_index EDW_OWB_COLLECTION_UTIL.numberTableType;
6116: l_sec_source_index_number number;
6117: l_start number;
6118: l_end number;
6119: l_len number;
6121: TYPE CurTyp IS REF CURSOR;
6122: cv CurTyp;
6123: l_metadata_version varchar2(80);
6124: Begin
6125: l_metadata_version:=EDW_OWB_COLLECTION_UTIL.get_metadata_version;
6126: if EDW_OWB_COLLECTION_UTIL.get_mapping_details(
6127: p_mapping_id
6128: ,l_hold_func
6129: ,l_hold_func_category
6122: cv CurTyp;
6123: l_metadata_version varchar2(80);
6124: Begin
6125: l_metadata_version:=EDW_OWB_COLLECTION_UTIL.get_metadata_version;
6126: if EDW_OWB_COLLECTION_UTIL.get_mapping_details(
6127: p_mapping_id
6128: ,l_hold_func
6129: ,l_hold_func_category
6130: ,l_hold_item
6140: ,l_hold_func_position
6141: ,l_hold_func_dvalue
6142: ,l_hold_number
6143: ,l_metadata_version)=false then
6144: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
6145: return false;
6146: end if;
6147: for i in 1..l_hold_number loop
6148: if l_hold_relation_name(i)=g_dim_name then
6147: for i in 1..l_hold_number loop
6148: if l_hold_relation_name(i)=g_dim_name then
6149: g_num_dim_derv_col_map_all:=g_num_dim_derv_col_map_all+1;
6150: g_dim_derv_col_map_all(g_num_dim_derv_col_map_all):=p_mapping_id;
6151: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_dim_derv_col,g_number_dim_derv_col,l_hold_item(i))=false then
6152: g_number_dim_derv_col:=g_number_dim_derv_col+1;
6153: g_dim_derv_col(g_number_dim_derv_col):=l_hold_item(i);
6154: g_dim_derv_col_map(g_number_dim_derv_col):=p_mapping_id;
6155: end if;
6154: g_dim_derv_col_map(g_number_dim_derv_col):=p_mapping_id;
6155: end if;
6156: end if;
6157: end loop;
6158: if EDW_OWB_COLLECTION_UTIL.get_sec_source_info(
6159: p_mapping_id,
6160: l_sec_source,
6161: l_sec_source_id ,
6162: l_sec_source_child ,
6166: l_sec_source_usage,
6167: l_sec_source_usage_name,
6168: l_sec_source_child_usage,
6169: l_sec_source_number) = false then
6170: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
6171: return false;
6172: end if;
6173: --also look at the filter clause
6174: --not checked
6203: write_to_log_file_n('Coulmn from filter for '||l_sec_source_usage_name(i)||' '||l_col);
6204: end if;
6205: g_num_dim_derv_col_map_all:=g_num_dim_derv_col_map_all+1;
6206: g_dim_derv_col_map_all(g_num_dim_derv_col_map_all):=p_mapping_id;
6207: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_dim_derv_col,g_number_dim_derv_col,l_col)=false then
6208: g_number_dim_derv_col:=g_number_dim_derv_col+1;
6209: g_dim_derv_col(g_number_dim_derv_col):=l_col;
6210: g_dim_derv_col_map(g_number_dim_derv_col):=p_mapping_id;
6211: end if;
6231: Begin
6232: g_number_before_update_table:=1;
6233: loop
6234: g_before_update_table(g_number_before_update_table):=g_before_update_table_name||g_number_before_update_table;
6235: if EDW_OWB_COLLECTION_UTIL.check_table(g_before_update_table(g_number_before_update_table))=false then
6236: g_before_update_table_final:=g_before_update_table(g_number_before_update_table);
6237: exit;
6238: else
6239: g_number_before_update_table:=g_number_before_update_table+1;
6263: end if;
6264: /*
6265: for every run of the load, the update data goes into a new table
6266: */
6267: if EDW_OWB_COLLECTION_UTIL.check_table(g_before_update_table_final)=false then
6268: if create_before_update_table=false then
6269: return false;
6270: end if;
6271: end if;
6273: for i in 1..g_number_dim_derv_col loop
6274: l_stmt:=l_stmt||','||g_dim_derv_col(i);
6275: end loop;
6276: for i in 1..g_number_dim_derv_pk_key loop
6277: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_dim_derv_col,g_number_dim_derv_col,g_dim_derv_pk_key(i))=false then
6278: l_stmt:=l_stmt||','||g_dim_derv_pk_key(i);
6279: end if;
6280: end loop;
6281: l_stmt:=l_stmt||',LAST_UPDATE_DATE) select /*+ORDERED USE_NL('||g_dim_name||')*/ '; --use nl?
6286: for i in 1..g_number_dim_derv_col loop
6287: l_stmt:=l_stmt||','||g_dim_name||'.'||g_dim_derv_col(i);
6288: end loop;
6289: for i in 1..g_number_dim_derv_pk_key loop
6290: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_dim_derv_col,g_number_dim_derv_col,g_dim_derv_pk_key(i))=false then
6291: l_stmt:=l_stmt||','||g_dim_name||'.'||g_dim_derv_pk_key(i);
6292: end if;
6293: end loop;
6294: l_stmt:=l_stmt||',SYSDATE from '||g_dim_name_hold||','||g_dim_name||
6333: for i in 1..g_number_dim_derv_col loop
6334: l_stmt:=l_stmt||','||g_dim_derv_col(i);
6335: end loop;
6336: for i in 1..g_number_dim_derv_pk_key loop
6337: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_dim_derv_col,g_number_dim_derv_col,g_dim_derv_pk_key(i))=false then
6338: l_stmt:=l_stmt||','||g_dim_derv_pk_key(i);
6339: end if;
6340: end loop;
6341: l_stmt:=l_stmt||',LAST_UPDATE_DATE from '||g_dim_name||' where 1=2';
6397: return null;
6398: End;
6399:
6400: function get_max_fk_density(p_dim_id number,p_src_fact_id number,p_dim_derv_map_id number) return number is
6401: l_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6402: l_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6403: l_number_fk number;
6404: l_distcnt number;
6405: l_density number;
6398: End;
6399:
6400: function get_max_fk_density(p_dim_id number,p_src_fact_id number,p_dim_derv_map_id number) return number is
6401: l_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6402: l_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
6403: l_number_fk number;
6404: l_distcnt number;
6405: l_density number;
6406: l_nullcnt number;
6409: l_tot_density number;
6410: l_fact_name varchar2(400);
6411: l_owner varchar2(400);
6412: Begin
6413: if EDW_OWB_COLLECTION_UTIL.get_fk_pk(p_src_fact_id,p_dim_id,p_dim_derv_map_id,l_fk,l_pk,l_number_fk)=false then
6414: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
6415: g_status:=false;
6416: return null;
6417: end if;
6410: l_fact_name varchar2(400);
6411: l_owner varchar2(400);
6412: Begin
6413: if EDW_OWB_COLLECTION_UTIL.get_fk_pk(p_src_fact_id,p_dim_id,p_dim_derv_map_id,l_fk,l_pk,l_number_fk)=false then
6414: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
6415: g_status:=false;
6416: return null;
6417: end if;
6418: if g_debug then
6426: if g_debug then
6427: write_to_log_file_n('The fk and density');
6428: end if;
6429: for i in 1..l_number_fk loop
6430: l_fact_name:=EDW_OWB_COLLECTION_UTIL.get_object_name(p_src_fact_id);
6431: l_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(l_fact_name);
6432: if EDW_OWB_COLLECTION_UTIL.get_column_stats(
6433: l_owner,
6434: l_fact_name,
6427: write_to_log_file_n('The fk and density');
6428: end if;
6429: for i in 1..l_number_fk loop
6430: l_fact_name:=EDW_OWB_COLLECTION_UTIL.get_object_name(p_src_fact_id);
6431: l_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(l_fact_name);
6432: if EDW_OWB_COLLECTION_UTIL.get_column_stats(
6433: l_owner,
6434: l_fact_name,
6435: l_fk(i),
6428: end if;
6429: for i in 1..l_number_fk loop
6430: l_fact_name:=EDW_OWB_COLLECTION_UTIL.get_object_name(p_src_fact_id);
6431: l_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(l_fact_name);
6432: if EDW_OWB_COLLECTION_UTIL.get_column_stats(
6433: l_owner,
6434: l_fact_name,
6435: l_fk(i),
6436: l_distcnt,
6462: Begin
6463: if g_debug then
6464: write_to_log_file_n('In create_prot_table '||get_time);
6465: end if;
6466: if EDW_OWB_COLLECTION_UTIL.check_table(p_table)=false then
6467: g_stmt:='create table '||p_table||' tablespace '||g_op_table_space;
6468: if g_parallel is not null then
6469: g_stmt:=g_stmt||' parallel (degree '||g_parallel||') ';
6470: end if;
6478: if g_parallel is not null then
6479: g_stmt:=g_stmt||' parallel (degree '||g_parallel||') ';
6480: end if;
6481: g_stmt:=g_stmt||' as select * from '||p_table;
6482: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_active)=false then
6483: null;
6484: end if;
6485: if g_debug then
6486: write_to_log_file_n('Going to execute '||g_stmt);
6499: Begin
6500: if g_debug then
6501: write_to_log_file_n('In drop_prot_table ');
6502: end if;
6503: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table)=false then
6504: null;
6505: end if;
6506: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_active)=false then
6507: null;
6502: end if;
6503: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table)=false then
6504: null;
6505: end if;
6506: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_active)=false then
6507: null;
6508: end if;
6509: return true;
6510: Exception when others then
6519: Begin
6520: if g_debug then
6521: write_to_log_file_n('In drop_restart_tables');
6522: end if;
6523: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
6524: null;
6525: end if;
6526: if EDW_OWB_COLLECTION_UTIL.drop_ilog_tables(g_ilog,'IL',g_bis_owner)=false then
6527: null;
6522: end if;
6523: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
6524: null;
6525: end if;
6526: if EDW_OWB_COLLECTION_UTIL.drop_ilog_tables(g_ilog,'IL',g_bis_owner)=false then
6527: null;
6528: end if;
6529: loop
6530: if EDW_OWB_COLLECTION_UTIL.check_table(g_before_update_table_name||l_count) then
6526: if EDW_OWB_COLLECTION_UTIL.drop_ilog_tables(g_ilog,'IL',g_bis_owner)=false then
6527: null;
6528: end if;
6529: loop
6530: if EDW_OWB_COLLECTION_UTIL.check_table(g_before_update_table_name||l_count) then
6531: if EDW_OWB_COLLECTION_UTIL.drop_table(g_before_update_table_name||l_count)=false then
6532: null;
6533: end if;
6534: l_count:=l_count+1;
6527: null;
6528: end if;
6529: loop
6530: if EDW_OWB_COLLECTION_UTIL.check_table(g_before_update_table_name||l_count) then
6531: if EDW_OWB_COLLECTION_UTIL.drop_table(g_before_update_table_name||l_count)=false then
6532: null;
6533: end if;
6534: l_count:=l_count+1;
6535: else
6560: l_stmt:=l_stmt||' as select row_id from '||g_ilog||' where status=1';
6561: if g_debug then
6562: write_to_log_file_n('Going to execute '||l_stmt||get_time);
6563: end if;
6564: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_small)=false then
6565: null;
6566: end if;
6567: execute immediate l_stmt;
6568: if g_debug then
6567: execute immediate l_stmt;
6568: if g_debug then
6569: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
6570: end if;
6571: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_ilog_small,instr(g_ilog_small,'.')+1,
6572: length(g_ilog_small)),substr(g_ilog_small,1,instr(g_ilog_small,'.')-1));
6573: return true;
6574: Exception when others then
6575: g_status_message:=sqlerrm;
6580:
6581: function create_dummy_ilog return boolean is
6582: l_stmt varchar2(5000);
6583: Begin
6584: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
6585: null;
6586: end if;
6587: l_stmt:='create table '||g_ilog||' tablespace '||g_op_table_space||
6588: ' as select rowid row_id, 1 status from dual ';
6605: write_to_log_file_n('In check_ll_snplog_col');
6606: end if;
6607: for i in 1..g_number_levels loop
6608: if g_levels(i)=g_lowest_level_global then
6609: if EDW_OWB_COLLECTION_UTIL.is_column_in_table(g_level_snapshot_logs(i),g_ltc_pk,g_table_owner) then
6610: g_ll_snplog_has_pk:=true;
6611: if g_debug then
6612: write_to_log_file_n('g_ll_snplog_has_pk set to TRUE');
6613: end if;
6637: write_to_log_file_n('In set_level_I_flag');
6638: end if;
6639: l_byte_size:=8;
6640: for i in 1..g_number_levels loop
6641: if EDW_OWB_COLLECTION_UTIL.get_table_avg_row_len(g_levels(i),g_table_owner,l_avg_row_len) then
6642: if g_debug then
6643: write_to_log_file_n('Level '||g_levels(i)||' avg_row_len='||l_avg_row_len);
6644: end if;
6645: if l_avg_row_len is not null and l_avg_row_len>0 then
6649: l_number_fk:=l_number_fk+1;
6650: end if;
6651: end loop;
6652: l_size:=l_byte_size*(l_number_fk+2);
6653: l_flag:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_size,l_avg_row_len,g_ok_switch_update);
6654: if l_flag then
6655: g_use_ltc_ilog(i):=true;
6656: else
6657: g_use_ltc_ilog(i):=false;
6692: g_lowest_level||' A,'||g_dim_name_temp||' B where A.rowid=B.row_id3 and B.operation_code1='||l_mode;
6693: if g_debug then
6694: write_to_log_file_n('Going to execute '||l_stmt||get_time);
6695: end if;
6696: if EDW_OWB_COLLECTION_UTIL.drop_table(g_levels_copy_low_hd_ins)=false then
6697: null;
6698: end if;
6699: execute immediate l_stmt;
6700: if g_debug then
6699: execute immediate l_stmt;
6700: if g_debug then
6701: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
6702: end if;
6703: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_levels_copy_low_hd_ins,instr(g_levels_copy_low_hd_ins,'.')+1,
6704: length(g_levels_copy_low_hd_ins)),substr(g_levels_copy_low_hd_ins,1,instr(g_levels_copy_low_hd_ins,'.')-1));
6705: return true;
6706: Exception when others then
6707: g_status_message:=sqlerrm;
6744: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
6745: end if;
6746: l_stmt:=l_stmt||' as select '||g_dim_pk||' from '||g_dim_name_temp||
6747: ' where operation_code1=0';
6748: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table1)=false then
6749: null;
6750: end if;
6751: if g_debug then
6752: write_to_log_file_n(l_stmt||get_time);
6754: execute immediate l_stmt;
6755: if g_debug then
6756: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
6757: end if;
6758: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_table1,instr(l_table1,'.')+1,
6759: length(l_table1)),substr(l_table1,1,instr(l_table1,'.')-1));
6760: --4161164 : remove IOT , replace with ordinary table and index
6761: --l_stmt:='create table '||l_table2||'('||g_dim_pk||' primary key,row_id) organization index '||
6762: l_stmt:='create table '||l_table2||
6769: l_stmt:=l_stmt||' /*+parallel(B,'||g_parallel||')*/ ';
6770: end if;
6771: l_stmt:=l_stmt||'A.'||g_dim_pk||',B.rowid row_id from '||l_table1||' A,'||
6772: g_dim_name||' B where A.'||g_dim_pk||'=B.'||g_dim_pk;
6773: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table2)=false then
6774: null;
6775: end if;
6776: if g_debug then
6777: write_to_log_file_n(l_stmt||get_time);
6779: execute immediate l_stmt;
6780: if g_debug then
6781: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
6782: --4161164 : remove IOT , replace with ordinary table and index
6783: EDW_OWB_COLLECTION_UTIL.create_iot_index(l_table2,g_dim_pk,g_op_table_space,g_parallel); end if;
6784: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_table2,instr(l_table2,'.')+1,
6785: length(l_table2)),substr(l_table2,1,instr(l_table2,'.')-1));
6786: l_stmt:='update /*+ordered use_nl(AA)*/ '||g_dim_name_temp||' AA set(row_id1,operation_code1)=(select row_id,'||
6787: '1 from '||l_table2||' where '||l_table2||'.'||g_dim_pk||'=AA.'||g_dim_pk||') where AA.'||g_dim_pk||
6780: if g_debug then
6781: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
6782: --4161164 : remove IOT , replace with ordinary table and index
6783: EDW_OWB_COLLECTION_UTIL.create_iot_index(l_table2,g_dim_pk,g_op_table_space,g_parallel); end if;
6784: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_table2,instr(l_table2,'.')+1,
6785: length(l_table2)),substr(l_table2,1,instr(l_table2,'.')-1));
6786: l_stmt:='update /*+ordered use_nl(AA)*/ '||g_dim_name_temp||' AA set(row_id1,operation_code1)=(select row_id,'||
6787: '1 from '||l_table2||' where '||l_table2||'.'||g_dim_pk||'=AA.'||g_dim_pk||') where AA.'||g_dim_pk||
6788: ' in (select '||g_dim_pk||' from '||l_table2||') ';
6788: ' in (select '||g_dim_pk||' from '||l_table2||') ';
6789: if g_debug then
6790: write_to_log_file_n(l_stmt||get_time);
6791: end if;
6792: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
6793: execute immediate l_stmt;
6794: if g_debug then
6795: write_to_log_file_n('Updated '||sql%rowcount||' rows '||get_time);
6796: end if;
6794: if g_debug then
6795: write_to_log_file_n('Updated '||sql%rowcount||' rows '||get_time);
6796: end if;
6797: commit;
6798: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table1)=false then
6799: null;
6800: end if;
6801: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table2)=false then
6802: null;
6797: commit;
6798: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table1)=false then
6799: null;
6800: end if;
6801: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table2)=false then
6802: null;
6803: end if;
6804: return true;
6805: Exception when others then
6810: End;
6811:
6812: function find_all_affected_levels(
6813: p_job_id number,
6814: p_affected_levels out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
6815: p_number_affected_levels out NOCOPY number) return boolean is
6816: l_hier EDW_OWB_COLLECTION_UTIL.varcharTableType;
6817: l_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
6818: l_parent_ltc_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6812: function find_all_affected_levels(
6813: p_job_id number,
6814: p_affected_levels out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
6815: p_number_affected_levels out NOCOPY number) return boolean is
6816: l_hier EDW_OWB_COLLECTION_UTIL.varcharTableType;
6817: l_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
6818: l_parent_ltc_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6819: l_child_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
6820: l_child_ltc_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6813: p_job_id number,
6814: p_affected_levels out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
6815: p_number_affected_levels out NOCOPY number) return boolean is
6816: l_hier EDW_OWB_COLLECTION_UTIL.varcharTableType;
6817: l_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
6818: l_parent_ltc_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6819: l_child_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
6820: l_child_ltc_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6821: l_number_hier number;
6814: p_affected_levels out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
6815: p_number_affected_levels out NOCOPY number) return boolean is
6816: l_hier EDW_OWB_COLLECTION_UTIL.varcharTableType;
6817: l_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
6818: l_parent_ltc_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6819: l_child_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
6820: l_child_ltc_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6821: l_number_hier number;
6822: l_parent_flat EDW_OWB_COLLECTION_UTIL.varcharTableType;
6815: p_number_affected_levels out NOCOPY number) return boolean is
6816: l_hier EDW_OWB_COLLECTION_UTIL.varcharTableType;
6817: l_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
6818: l_parent_ltc_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6819: l_child_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
6820: l_child_ltc_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6821: l_number_hier number;
6822: l_parent_flat EDW_OWB_COLLECTION_UTIL.varcharTableType;
6823: l_child_flat EDW_OWB_COLLECTION_UTIL.varcharTableType;
6816: l_hier EDW_OWB_COLLECTION_UTIL.varcharTableType;
6817: l_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
6818: l_parent_ltc_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6819: l_child_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
6820: l_child_ltc_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6821: l_number_hier number;
6822: l_parent_flat EDW_OWB_COLLECTION_UTIL.varcharTableType;
6823: l_child_flat EDW_OWB_COLLECTION_UTIL.varcharTableType;
6824: l_number_flat number;
6818: l_parent_ltc_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6819: l_child_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
6820: l_child_ltc_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6821: l_number_hier number;
6822: l_parent_flat EDW_OWB_COLLECTION_UTIL.varcharTableType;
6823: l_child_flat EDW_OWB_COLLECTION_UTIL.varcharTableType;
6824: l_number_flat number;
6825: l_children EDW_OWB_COLLECTION_UTIL.varcharTableType;
6826: l_number_children number;
6819: l_child_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
6820: l_child_ltc_id EDW_OWB_COLLECTION_UTIL.numberTableType;
6821: l_number_hier number;
6822: l_parent_flat EDW_OWB_COLLECTION_UTIL.varcharTableType;
6823: l_child_flat EDW_OWB_COLLECTION_UTIL.varcharTableType;
6824: l_number_flat number;
6825: l_children EDW_OWB_COLLECTION_UTIL.varcharTableType;
6826: l_number_children number;
6827: l_table varchar2(200);
6821: l_number_hier number;
6822: l_parent_flat EDW_OWB_COLLECTION_UTIL.varcharTableType;
6823: l_child_flat EDW_OWB_COLLECTION_UTIL.varcharTableType;
6824: l_number_flat number;
6825: l_children EDW_OWB_COLLECTION_UTIL.varcharTableType;
6826: l_number_children number;
6827: l_table varchar2(200);
6828: Begin
6829: if g_debug then
6829: if g_debug then
6830: write_to_log_file_n('In find_all_affected_levels '||get_time);
6831: end if;
6832: p_number_affected_levels:=0;
6833: if EDW_OWB_COLLECTION_UTIL.get_dim_hier_levels(g_dim_name,l_hier,l_parent_ltc,l_parent_ltc_id,
6834: l_child_ltc,l_child_ltc_id,l_number_hier)=false then
6835: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
6836: return false;
6837: end if;
6831: end if;
6832: p_number_affected_levels:=0;
6833: if EDW_OWB_COLLECTION_UTIL.get_dim_hier_levels(g_dim_name,l_hier,l_parent_ltc,l_parent_ltc_id,
6834: l_child_ltc,l_child_ltc_id,l_number_hier)=false then
6835: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
6836: return false;
6837: end if;
6838: if g_debug then
6839: write_to_log_file_n('The hier and levels');
6848: if get_parent(g_levels(i),l_parent_ltc,l_child_ltc,l_number_hier,l_children,l_number_children)=false then
6849: return false;
6850: end if;
6851: for j in 1..l_number_children loop
6852: if EDW_OWB_COLLECTION_UTIL.value_in_table(l_parent_flat,l_child_flat,l_number_flat,l_children(j),
6853: g_levels(i))=false then
6854: l_number_flat:=l_number_flat+1;
6855: l_parent_flat(l_number_flat):=l_children(j);
6856: l_child_flat(l_number_flat):=g_levels(i);
6871: l_table:=g_bis_owner||'.LTFC_'||l_child_ltc_id(j)||'_'||l_parent_ltc_id(j);
6872: else
6873: l_table:=g_bis_owner||'.LTFC_'||l_child_ltc_id(j)||'_'||l_parent_ltc_id(j)||'_'||p_job_id;
6874: end if;
6875: if EDW_OWB_COLLECTION_UTIL.check_table(l_table) then
6876: g_number_objects_to_drop:=g_number_objects_to_drop+1;
6877: g_objects_to_drop(g_number_objects_to_drop):=l_table;
6878: if EDW_OWB_COLLECTION_UTIL.value_in_table(p_affected_levels,p_number_affected_levels,
6879: l_parent_ltc(j))=false then
6874: end if;
6875: if EDW_OWB_COLLECTION_UTIL.check_table(l_table) then
6876: g_number_objects_to_drop:=g_number_objects_to_drop+1;
6877: g_objects_to_drop(g_number_objects_to_drop):=l_table;
6878: if EDW_OWB_COLLECTION_UTIL.value_in_table(p_affected_levels,p_number_affected_levels,
6879: l_parent_ltc(j))=false then
6880: p_number_affected_levels:=p_number_affected_levels+1;
6881: p_affected_levels(p_number_affected_levels):=l_parent_ltc(j);
6882: end if;
6881: p_affected_levels(p_number_affected_levels):=l_parent_ltc(j);
6882: end if;
6883: for k in 1..l_number_flat loop
6884: if l_child_flat(k)=l_parent_ltc(j) then
6885: if EDW_OWB_COLLECTION_UTIL.value_in_table(p_affected_levels,p_number_affected_levels,
6886: l_parent_flat(k))=false then
6887: p_number_affected_levels:=p_number_affected_levels+1;
6888: p_affected_levels(p_number_affected_levels):=l_parent_flat(k);
6889: end if;
6905: write_to_log_file_n('error in find_all_affected_levels '||g_status_message);
6906: return false;
6907: End;
6908:
6909: function get_parent(p_child_level varchar2,p_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType,
6910: p_child_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType, p_number_hier number,
6911: p_parent out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType, p_number_parent out NOCOPY number) return boolean is
6912: loop_end number;
6913: l_parent EDW_OWB_COLLECTION_UTIL.varcharTableType;
6906: return false;
6907: End;
6908:
6909: function get_parent(p_child_level varchar2,p_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType,
6910: p_child_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType, p_number_hier number,
6911: p_parent out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType, p_number_parent out NOCOPY number) return boolean is
6912: loop_end number;
6913: l_parent EDW_OWB_COLLECTION_UTIL.varcharTableType;
6914: l_number_parent number;
6907: End;
6908:
6909: function get_parent(p_child_level varchar2,p_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType,
6910: p_child_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType, p_number_hier number,
6911: p_parent out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType, p_number_parent out NOCOPY number) return boolean is
6912: loop_end number;
6913: l_parent EDW_OWB_COLLECTION_UTIL.varcharTableType;
6914: l_number_parent number;
6915: Begin
6909: function get_parent(p_child_level varchar2,p_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType,
6910: p_child_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType, p_number_hier number,
6911: p_parent out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType, p_number_parent out NOCOPY number) return boolean is
6912: loop_end number;
6913: l_parent EDW_OWB_COLLECTION_UTIL.varcharTableType;
6914: l_number_parent number;
6915: Begin
6916: p_number_parent:=0;
6917: for i in 1..p_number_hier loop
6915: Begin
6916: p_number_parent:=0;
6917: for i in 1..p_number_hier loop
6918: if p_child_level=p_child_ltc(i) then
6919: if EDW_OWB_COLLECTION_UTIL.value_in_table(p_parent,p_number_parent,p_parent_ltc(i))=false then
6920: p_number_parent:=p_number_parent+1;
6921: p_parent(p_number_parent):=p_parent_ltc(i);
6922: end if;
6923: end if;
6947: --cut off kept at 5%
6948: Begin
6949: l_table:=substr(g_ilog,instr(g_ilog,'.')+1,length(g_ilog));
6950: l_owner:=substr(g_ilog,1,instr(g_ilog,'.')-1);
6951: l_total_records:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(l_table,l_owner);
6952: if g_collection_size>0 then
6953: if l_total_records>0 then
6954: l_percentage:=100*(g_collection_size/l_total_records);
6955: if l_percentage
7030: l_level_table varchar2(80);
7031: l_level_child_table varchar2(80);
7032: l_skip_table varchar2(80);
7033: l_run number;
7034: l_consider_snapshot EDW_OWB_COLLECTION_UTIL.varcharTableType;
7035: Begin
7036: if g_debug then
7037: write_to_log_file_n('In read_options_table '||p_table_name);
7038: end if;
7309: Begin
7310: if g_debug then
7311: write_to_log_file_n('In make_ok_from_main_ok '||p_main_ok_table_name||' '||p_low_end||' '||p_high_end);
7312: end if;
7313: if EDW_OWB_COLLECTION_UTIL.make_ilog_from_main_ilog(
7314: g_ilog,
7315: p_main_ok_table_name,
7316: p_low_end,
7317: p_high_end,
7320: g_parallel,
7321: l_ilog_number)=false then
7322: return false;
7323: end if;
7324: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_ilog,' status=1 ')=2 then
7325: g_skip_ilog_update:=true;
7326: end if;
7327: return true;
7328: Exception when others then
7332: End;
7333:
7334: function drop_input_tables(p_table_name varchar2) return boolean is
7335: Begin
7336: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name)=false then
7337: null;
7338: end if;
7339: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name||'_LT')=false then
7340: null;
7335: Begin
7336: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name)=false then
7337: null;
7338: end if;
7339: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name||'_LT')=false then
7340: null;
7341: end if;
7342: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name||'_CT')=false then
7343: null;
7338: end if;
7339: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name||'_LT')=false then
7340: null;
7341: end if;
7342: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name||'_CT')=false then
7343: null;
7344: end if;
7345: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name||'_SK')=false then
7346: null;
7341: end if;
7342: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name||'_CT')=false then
7343: null;
7344: end if;
7345: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name||'_SK')=false then
7346: null;
7347: end if;
7348: return true;
7349: Exception when others then
7356: called when being run as a job
7357: */
7358: function set_session_parameters return boolean is
7359: Begin
7360: if EDW_OWB_COLLECTION_UTIL.set_session_parameters(g_hash_area_size,g_sort_area_size,
7361: g_trace,g_parallel)=false then
7362: return false;
7363: end if;
7364: return true;
7384: g_ilog:=substr(g_ilog,1,length(g_ilog)-1);
7385: else
7386: g_ilog:=g_ilog||'A';
7387: end if;
7388: if EDW_OWB_COLLECTION_UTIL.put_rownum_in_ilog_table(
7389: g_ilog,
7390: l_ilog_table,
7391: g_op_table_space,
7392: g_parallel)=false then
7405: p_temp_exe_name varchar2,
7406: p_bis_short_name varchar2
7407: ) return boolean is
7408: l_exe_file_name varchar2(200);
7409: l_parameter EDW_OWB_COLLECTION_UTIL.varcharTableType;
7410: l_parameter_value_set EDW_OWB_COLLECTION_UTIL.varcharTableType;
7411: l_number_parameters number;
7412: Begin
7413: l_exe_file_name:='EDW_SUMMARY_COLLECT.COLLECT_DIMENSION';
7406: p_bis_short_name varchar2
7407: ) return boolean is
7408: l_exe_file_name varchar2(200);
7409: l_parameter EDW_OWB_COLLECTION_UTIL.varcharTableType;
7410: l_parameter_value_set EDW_OWB_COLLECTION_UTIL.varcharTableType;
7411: l_number_parameters number;
7412: Begin
7413: l_exe_file_name:='EDW_SUMMARY_COLLECT.COLLECT_DIMENSION';
7414: l_parameter(1):='p_dim_name';
7421: l_parameter_value_set(4):='FND_NUMBER';
7422: l_parameter(5):='p_ok_high_end';
7423: l_parameter_value_set(5):='FND_NUMBER';
7424: l_number_parameters:=5;
7425: if EDW_OWB_COLLECTION_UTIL.create_conc_program(
7426: p_temp_conc_name,
7427: p_temp_conc_short_name,
7428: p_temp_exe_name,
7429: l_exe_file_name,
7442: End;
7443:
7444: function insert_L_ilog_parallel_dd(p_multi_thread boolean) return boolean is
7445: --
7446: l_status edw_owb_collection_util.varcharTableType;
7447: l_num_status number;
7448: l_job_id number;
7449: l_job_running boolean;
7450: l_ul_table varchar2(200);
7455: if g_debug then
7456: write_to_log_file_n('In insert_L_ilog_parallel_dd'||get_time);
7457: end if;
7458: --see if the process for the lowest level has finished
7459: if edw_owb_collection_util.query_table_cols(g_dd_status_table,'status',' where parent_ltc_id='||g_lowest_level_id,
7460: l_status,l_num_status)=false then
7461: return false;
7462: end if;
7463: if substr(l_status(1),1,3)='ERR' then
7466: end if;
7467: if l_status(1)<>'DONE' then
7468: --job is still running
7469: l_num_status:=0;
7470: if edw_owb_collection_util.query_table_cols(g_dd_status_table,'job_id',' where parent_ltc_id='||g_lowest_level_id,
7471: l_status,l_num_status)=false then
7472: g_status_message:=edw_owb_collection_util.g_status_message;
7473: return false;
7474: end if;
7468: --job is still running
7469: l_num_status:=0;
7470: if edw_owb_collection_util.query_table_cols(g_dd_status_table,'job_id',' where parent_ltc_id='||g_lowest_level_id,
7471: l_status,l_num_status)=false then
7472: g_status_message:=edw_owb_collection_util.g_status_message;
7473: return false;
7474: end if;
7475: l_job_id:=to_number(l_status(1));
7476: l_job_running:=false;
7473: return false;
7474: end if;
7475: l_job_id:=to_number(l_status(1));
7476: l_job_running:=false;
7477: if edw_owb_collection_util.check_job_status(l_job_id)='Y' then
7478: if edw_owb_collection_util.check_table(g_bis_owner||'.TAB_MARKER_DD_'||g_lowest_level_id) then
7479: l_job_running:=true;
7480: end if;
7481: end if;
7474: end if;
7475: l_job_id:=to_number(l_status(1));
7476: l_job_running:=false;
7477: if edw_owb_collection_util.check_job_status(l_job_id)='Y' then
7478: if edw_owb_collection_util.check_table(g_bis_owner||'.TAB_MARKER_DD_'||g_lowest_level_id) then
7479: l_job_running:=true;
7480: end if;
7481: end if;
7482: if l_job_running then
7479: l_job_running:=true;
7480: end if;
7481: end if;
7482: if l_job_running then
7483: if edw_owb_collection_util.wait_on_jobs(l_job_id,g_sleep_time,'JOB')=false then
7484: return false;
7485: end if;
7486: if edw_owb_collection_util.query_table_cols(g_dd_status_table,'status',' where parent_ltc_id='||g_lowest_level_id,
7487: l_status,l_num_status)=false then
7482: if l_job_running then
7483: if edw_owb_collection_util.wait_on_jobs(l_job_id,g_sleep_time,'JOB')=false then
7484: return false;
7485: end if;
7486: if edw_owb_collection_util.query_table_cols(g_dd_status_table,'status',' where parent_ltc_id='||g_lowest_level_id,
7487: l_status,l_num_status)=false then
7488: return false;
7489: end if;
7490: if substr(l_status(1),1,3)='ERR' then
7492: return false;
7493: end if;
7494: else
7495: --process has crashed
7496: if edw_owb_collection_util.terminate_job(l_job_id)=false then
7497: null;
7498: end if;
7499: if g_debug then
7500: write_to_log_file_n(l_job_id||' could have crashed or not started. Try serial');