[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_update_type varchar2,
p_mode varchar2,
p_explain_plan_check boolean,
p_fact_dlog varchar2,
p_key_set number,
p_instance_type varchar2,
p_load_pk number,
p_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_skip_cols number,
p_fresh_restart boolean,
p_op_table_space varchar2,
p_da_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_da_cols number,
p_da_table varchar2,
p_pp_table varchar2,
p_master_instance varchar2,
p_rollback varchar2,
p_skip_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_skip_levels number,
p_smart_update boolean,
p_fk_use_nl number,
p_fact_smart_update number,
p_auto_dang_table_extn varchar2,
p_log_dang_keys boolean,
p_create_parent_table_records boolean,
p_smart_update_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_smart_update_cols number,
p_check_fk_change boolean,
p_stg_join_nl_percentage number,
p_ok_switch_update number,
p_stg_make_copy_percentage number,
p_hash_area_size number,
p_sort_area_size number,
p_trace boolean,
p_read_cfig_options boolean,
p_min_job_load_size number,
p_sleep_time number,
p_thread_type varchar2,
p_max_threads number,
p_job_status_table varchar2,
p_analyze_frequency number,
p_parallel_drill_down boolean,
p_dd_status_table varchar2
) IS
Begin
if p_max_threads>1 then
--multi threading
COLLECT_MULTI_THREAD(
p_object_name,
p_mapping_id,
p_map_type,
p_primary_src,
p_primary_target,
p_primary_target_name,
p_object_type,
p_conc_id,
p_conc_program_name,
p_status,
p_fact_audit,
p_net_change,
p_fact_audit_name,
p_net_change_name,
p_fact_audit_is_name,
p_net_change_is_name,
p_debug,
p_duplicate_collect,
p_execute_flag,
p_request_id,
p_collection_size,
p_parallel,
p_table_owner,
p_bis_owner,
p_temp_log,
p_forall_size,
p_update_type,
p_mode,
p_explain_plan_check,
p_fact_dlog,
p_key_set,
p_instance_type,
p_load_pk,
p_skip_cols,
p_number_skip_cols,
p_fresh_restart,
p_op_table_space,
p_da_cols,
p_number_da_cols,
p_da_table,
p_pp_table,
p_master_instance,
p_rollback,
p_skip_levels,
p_number_skip_levels,
p_smart_update,
p_fk_use_nl,
p_fact_smart_update,
p_auto_dang_table_extn,
p_log_dang_keys,
p_create_parent_table_records,
p_smart_update_cols,
p_number_smart_update_cols,
p_check_fk_change,
p_stg_join_nl_percentage,
p_ok_switch_update,
p_stg_make_copy_percentage,
p_hash_area_size,
p_sort_area_size,
p_trace,
p_read_cfig_options,
p_min_job_load_size,
p_sleep_time,
p_thread_type,
p_max_threads,
p_job_status_table,
p_analyze_frequency,
p_parallel_drill_down,
p_dd_status_table
);
p_update_type,
p_mode,
p_explain_plan_check,
p_fact_dlog,
p_key_set,
p_instance_type,
p_load_pk,
p_skip_cols,
p_number_skip_cols,
p_fresh_restart,
p_op_table_space,
p_da_cols,
p_number_da_cols,
p_da_table,
p_pp_table,
p_master_instance,
p_rollback,
p_skip_levels,
p_number_skip_levels,
p_smart_update,
p_fk_use_nl,
p_fact_smart_update,
p_auto_dang_table_extn,
p_log_dang_keys,
p_create_parent_table_records,
p_smart_update_cols,
p_number_smart_update_cols,
p_check_fk_change,
p_stg_join_nl_percentage,
p_ok_switch_update,
p_stg_make_copy_percentage,
p_read_cfig_options,
p_analyze_frequency
);
p_update_type varchar2,
p_mode varchar2,
p_explain_plan_check boolean,
p_fact_dlog varchar2,
p_key_set number,
p_instance_type varchar2,
p_load_pk number,
p_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_skip_cols number,
p_fresh_restart boolean,
p_op_table_space varchar2,
p_da_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_da_cols number,
p_da_table varchar2,
p_pp_table varchar2,
p_master_instance varchar2,
p_rollback varchar2,
p_skip_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_skip_levels number,
p_smart_update boolean,
p_fk_use_nl number,
p_fact_smart_update number,
p_auto_dang_table_extn varchar2,
p_log_dang_keys boolean,
p_create_parent_table_records boolean,
p_smart_update_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_smart_update_cols number,
p_check_fk_change boolean,
p_stg_join_nl_percentage number,
p_ok_switch_update number,
p_stg_make_copy_percentage number,
p_hash_area_size number,
p_sort_area_size number,
p_trace boolean,
p_read_cfig_options boolean,
p_min_job_load_size number,
p_sleep_time number,
p_thread_type varchar2,
p_max_threads number,
p_job_status_table varchar2,
p_analyze_frequency number,
p_parallel_drill_down boolean,
p_dd_status_table varchar2
) IS
l_input_table varchar2(200);
p_update_type,
p_mode,
p_explain_plan_check,
p_fact_dlog,
p_key_set,
p_instance_type,
p_load_pk,
p_skip_cols,
p_number_skip_cols,
p_fresh_restart,
p_op_table_space,
p_da_cols,
p_number_da_cols,
p_da_table,
p_pp_table,
p_master_instance,
p_rollback,
p_skip_levels,
p_number_skip_levels,
p_smart_update,
p_fk_use_nl,
p_fact_smart_update,
p_auto_dang_table_extn,
p_log_dang_keys,
p_create_parent_table_records,
p_smart_update_cols,
p_number_smart_update_cols,
p_check_fk_change,
p_stg_join_nl_percentage,
p_ok_switch_update,
p_stg_make_copy_percentage,
null,--ok table name
p_hash_area_size,
p_sort_area_size,
p_trace,
p_read_cfig_options,
p_job_status_table,
null,
null,
null,
p_sleep_time,
g_parallel_drill_down
)=false then
p_status:=false;
if EDW_OWB_COLLECTION_UTIL.update_load_input_table(
l_input_table,
l_ok_table,
g_max_round,
g_update_dlog_lookup_table,
g_dlog_has_data,
g_total_records,
g_stg_copy_table_flag
)=false then
g_status_message:=EDW_OWB_COLLECTION_UTIL.get_status_message;
if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_dlog_lookup_table)=false then
null;
insert_into_load_progress_d(g_load_pk,g_primary_target_name,'Pre Mapping Collection Hook',sysdate,null,'MAPPING',
'PRE-MAP',1010,'I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,1010,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,'Read Metadata',sysdate,null,'MAPPING',
'METADATA-READ',1020,'I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,1020,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,'Recover from any Previous Error',sysdate,null,'MAPPING',
'ERROR-RECOVERY',1040,'I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,1040,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,'Check Number of Records to Load',sysdate,null,'MAPPING',
'LOAD-CHECK',1050,'I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,1050,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,'Create Data Alignment Tables',sysdate,null,'MAPPING',
'CREATE-TABLE','DATC','I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,'DATC','U');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,'DATC','U');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,'DATC','U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,'Duplicate Check',sysdate,null,'MAPPING',
'DUPLICATE-CHECK',1100,'I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,1100,'U');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,1100,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,'Post Mapping Collection Hook',sysdate,null,'HOOK',
'POST-MAP',1070,'I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,1070,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Read Metadata',sysdate,null,'MAPPING',
'METADATA-READ',g_jobid_stmt||'1020','I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'1020','U');
p_update_type varchar2,
p_mode varchar2,
p_explain_plan_check boolean,
p_fact_dlog varchar2,
p_key_set number,
p_instance_type varchar2,
p_load_pk number,
p_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_skip_cols number,
p_fresh_restart boolean,
p_op_table_space varchar2,
p_da_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_da_cols number,
p_da_table varchar2,
p_pp_table varchar2,
p_master_instance varchar2,
p_rollback varchar2,
p_skip_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_skip_levels number,
p_smart_update boolean,
p_fk_use_nl number,
p_fact_smart_update number,
p_auto_dang_table_extn varchar2,
p_log_dang_keys boolean,
p_create_parent_table_records boolean,
p_smart_update_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_smart_update_cols number,
p_check_fk_change boolean,
p_stg_join_nl_percentage number,
p_ok_switch_update number,
p_stg_make_copy_percentage number,
p_read_cfig_options boolean,
p_analyze_frequency number
) IS
begin
g_object_name:=p_object_name; --name of the dim or fact
g_update_type :=p_update_type;
g_smart_update:=p_smart_update;
g_fact_smart_update:=p_fact_smart_update;
g_smart_update_cols:=p_smart_update_cols;
g_number_smart_update_cols:=p_number_smart_update_cols;
g_ok_switch_update:=p_ok_switch_update;
write_to_log_file('g_update_type='||g_update_type);
if g_smart_update then
write_to_log_file('g_smart_update TRUE');
write_to_log_file('g_smart_update FALSE');
write_to_log_file('g_fact_smart_update '||g_fact_smart_update);
write_to_log_file('Smart Update columns');
for i in 1..g_number_smart_update_cols loop
write_to_log_file(g_smart_update_cols(i));
write_to_log_file('g_ok_switch_update='||g_ok_switch_update);
insert_into_load_progress_d(g_load_pk,g_primary_target_name,'Pre Mapping Collection Hook',sysdate,null,'MAPPING',
'PRE-MAP',1010,'I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,1010,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,'Read Metadata',sysdate,null,'MAPPING',
'METADATA-READ',1020,'I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,1020,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,'Recover from any Previous Error',sysdate,null,'MAPPING',
'ERROR-RECOVERY',1040,'I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,1040,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,'Check Number of Records to Load',sysdate,null,'MAPPING',
'LOAD-CHECK',1050,'I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,1050,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,'Create Data Alignment Tables',sysdate,null,'MAPPING',
'CREATE-TABLE','DATC','I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,'DATC','U');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,'DATC','U');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,'DATC','U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,'Duplicate Check',sysdate,null,'MAPPING',
'DUPLICATE-CHECK',1100,'I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,1100,'U');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,1100,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Generate Explain Plan',sysdate,null,'MAPPING',
'EXPLAIN-PLAN',g_jobid_stmt||'1080','I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'1080','U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Generate Dimension Lookup Tables',sysdate,
null,'MAPPING','LOOKUP',g_jobid_stmt||'1090','I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'1090','U');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'1080','U');
select_fact_audit;--make the stmts
write_to_log_file_n('select_fact_audit has errors');
select_net_change;--make the stmts
write_to_log_file_n('select_net_change ERROR');
make_data_into_dlog_stmt;--if this is a derived fact then we need to move the data into dlog for updates
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Mark Records for Processing',sysdate,null,
'MAPPING','UPDATE',g_jobid_stmt||'MKRP'||l_count,'I');
if g_skip_ilog_update=false then
make_records_processing; --makes ready records to processing.
g_skip_ilog_update:=false;
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'MKRP'||l_count,'U');
g_skip_ilog_update:=false;
if g_type_ok_generation='UPDATE' then
--else, make_records_processing will create the ok table
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Mark Records as Processed',sysdate,null,
'MAPPING','UPDATE',g_jobid_stmt||'MKRPP'||l_count,'I');
if update_ok_status_2= false then
return false;
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'MKRPP'||l_count,'U');
we only need to insert into temp log and not worry about updates. the logic for updates are handled
in get_lowest_level_log in EDW_ALL_COLLECT
this ing is only for the lowest level. so we need to control that with g_temp_log
also true for facts
*/
if g_temp_log then
if EDW_OWB_COLLECTION_UTIL.insert_temp_log_table(
g_object_name,
g_object_type,
g_conc_program_id,
g_ins_instance_name,
g_ins_request_id_table,
g_ins_rows_ready,
g_ins_rows_processed,
g_ins_rows_collected,
g_ins_rows_dangling,
g_ins_rows_duplicate,
g_ins_rows_error,
g_total_records,
g_total_insert,
g_total_update,
g_total_delete,
g_number_ins_req_coll) = false then
g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Load Into PP Table',sysdate,null,
'MAPPING','INSERT',g_jobid_stmt||'LOADPP','I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'LOADPP','U');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'LOADPP','U');
l_stmt:='select 1 from '||g_surr_table||' having count(*)>1 group by row_id';
l_stmt:=l_stmt||' as select row_id';
l_stmt:=l_stmt||' as select * from '||l_table1;
l_stmt:=l_stmt||' as select ';
if g_update_type='DELETE-INSERT' then
l_stmt:=l_stmt||','||g_opcode_table||'.'||g_fstgPKName||' '||g_fstgPKName||','||
g_opcode_table||'.CREATION_DATE CREATION_DATE';
l_stmt:=' select ';
procedure make_hd_insert_stmt is
l_divide number:=2;
write_to_log_file_n('In make_hd_insert_stmt');
if g_smart_update then
if edw_owb_collection_util.get_db_columns_for_table(
g_factTableName,
l_cols,
l_cols_datatype,
l_data_length,
l_num_distinct,
l_num_nulls,
l_avg_col_length,
l_number_cols,
g_table_owner)=false then
g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
l_number_itemset_cols:=g_number_smart_update_cols;
l_itemset_cols:=g_smart_update_cols;
',cannot do smart update');
g_smart_update:=false;
if g_smart_update then
l_count:=0;
if g_fact_smart_update is not null and g_fact_smart_update>0 then
if l_count>g_fact_smart_update then
g_smart_update:=false;
if g_smart_update then
write_to_log_file_n('Smart update TRUE');
write_to_log_file_n('Smart update FALSE');
if g_smart_update then
g_hd_insert_stmt:='create table '||g_hold_table_temp||' tablespace '||g_op_table_space;
g_hd_insert_stmt:='create table '||g_hold_table||' tablespace '||g_op_table_space;
g_hd_insert_stmt:=g_hd_insert_stmt||' storage(initial '||l_extent||' next '||
l_extent||' pctincrease 0 MAXEXTENTS 2147483645) ';
g_hd_insert_stmt:=g_hd_insert_stmt||' parallel (degree '||g_parallel||') ';
g_hd_insert_stmt:=g_hd_insert_stmt||' as select ';
g_hd_insert_stmt:=g_hd_insert_stmt||' /*+ORDERED USE_NL('||g_fstgTableName||')*/ ';
g_hd_insert_stmt:=g_hd_insert_stmt||' /*+ORDERED*/ ';
g_hd_insert_stmt:=g_hd_insert_stmt||' /*+PARALLEL ('||g_fstgTableName||','||g_parallel||')*/ ';
if g_update_type='DELETE-INSERT' then
if g_fact_mapping_columns(i) <> g_factPKNameKey then
g_hd_insert_stmt:=g_hd_insert_stmt||g_fstg_mapping_columns(i)||' '||g_fact_mapping_columns(i)||',';
g_hd_insert_stmt:=g_hd_insert_stmt||g_fstg_mapping_columns(i)||' '||g_fact_mapping_columns(i)||',';
g_hd_insert_stmt:=g_hd_insert_stmt||g_fstg_mapping_columns(i)||' '||g_fact_mapping_columns(i)||',';
if g_update_type='DELETE-INSERT' then
g_hd_insert_stmt:=g_hd_insert_stmt||g_surr_table||'.'||g_fstgPKNameKey||' '||g_factPKNameKey||',';
g_hd_insert_stmt:=g_hd_insert_stmt||g_surr_table||'.CREATION_DATE CREATION_DATE,';
g_hd_insert_stmt:=g_hd_insert_stmt||g_surr_table||'.'||g_fstgPKNameKey||' '||g_factPKNameKey||',';
g_hd_insert_stmt:=g_hd_insert_stmt||g_surr_table||'.'||g_fstgActualFKName(i)||' '||g_factFKName(i)||',';
g_hd_insert_stmt:=g_hd_insert_stmt||g_fstg_fk_load_value(i)||' '||g_factFKName(i)||',';
g_hd_insert_stmt:=g_hd_insert_stmt||g_fstgTableName||'.'||g_fstgActualFKName(i)||' '||g_factFKName(i)||',';
g_hd_insert_stmt:=g_hd_insert_stmt||g_surr_table||'.row_id1 row_id1 from '||g_surr_table||',';
g_hd_insert_stmt:=g_hd_insert_stmt||g_stg_copy_table||' '||g_fstgTableName;
g_hd_insert_stmt:=g_hd_insert_stmt||' where '||g_surr_table||'.operation_code=1 and '||
g_fstgTableName||'.rowid='||g_surr_table||'.row_id_copy';
g_hd_insert_stmt:=g_hd_insert_stmt||g_user_measure_table||' '||g_fstgTableName;
g_hd_insert_stmt:=g_hd_insert_stmt||' where '||g_surr_table||'.operation_code=1 and '||
g_fstgTableName||'.rowid='||g_surr_table||'.row_id_copy';
g_hd_insert_stmt:=g_hd_insert_stmt||g_fstgTableName;
g_hd_insert_stmt:=g_hd_insert_stmt||' where '||g_surr_table||'.operation_code=1 and '||
g_fstgTableName||'.rowid='||g_surr_table||'.row_id';
g_hd_insert_stmt:=g_hd_insert_stmt||' group by ';
g_hd_insert_stmt:=g_hd_insert_stmt||g_fstgTableName||'.'||g_groupby_cols(i)||',';
g_hd_insert_stmt:=g_hd_insert_stmt||g_surr_table||'.'||g_fstgActualFKName(i)||',';
g_hd_insert_stmt:=g_hd_insert_stmt||g_fstgTableName||'.'||g_fstgActualFKName(i)||',';
g_hd_insert_stmt:=g_hd_insert_stmt||g_surr_table||'.row_id1 ';
write_to_log_file_n('g_hd_insert_stmt is '||g_hd_insert_stmt);
procedure make_insert_update_stmt IS
l_stmt varchar2(30000);
write_to_debug_n('In make_insert_update_stmt');
g_num_ff_map_cols,'LAST_UPDATE_DATE')= false then
g_last_update_date_flag:=true;
g_last_update_date_flag:=false;
l_stmt:=l_stmt||' as select ';
g_insert_stmt_ctas:='create table '||g_insert_ctas_table||' tablespace '||g_op_table_space;
g_insert_stmt_ctas:=g_insert_stmt_ctas||' parallel (degree '||g_parallel||')';
g_insert_stmt_ctas:=g_insert_stmt_ctas||' as ';
g_insert_stmt_row:=' insert into '||g_factTableName||'( ';
l_stmt:=' insert ';
g_insert_stmt_row:=g_insert_stmt_row||g_factPKNameKey||',';
g_insert_stmt_row:=g_insert_stmt_row||g_fact_mapping_columns(i)||',';
g_insert_stmt_row:=substr(g_insert_stmt_row,1,length(g_insert_stmt_row)-1);
g_insert_stmt_row:=g_insert_stmt_row||','||g_factFKName(i)||' ';
g_insert_stmt_row:=g_insert_stmt_row||',CREATION_DATE ';
if g_last_update_date_flag then
l_stmt:=l_stmt||',LAST_UPDATE_DATE ';
g_insert_stmt_row:=g_insert_stmt_row||',LAST_UPDATE_DATE ';
l_stmt:=l_stmt||' ) select ';
g_insert_stmt_row:=g_insert_stmt_row||' ) select ';
g_insert_stmt_ctas:=g_insert_stmt_ctas||' /*+ORDERED USE_NL('||g_fstgTableName||')*/ ';
g_insert_stmt_ctas:=g_insert_stmt_ctas||' /*+ORDERED*/ ';
g_insert_stmt_ctas:=g_insert_stmt_ctas||' /*+PARALLEL ('||g_fstgTableName||','||
g_parallel||')*/ ';
g_insert_stmt_row:=g_insert_stmt_row||g_factPKNameKey||',';
g_insert_stmt_ctas:=g_insert_stmt_ctas||g_surr_table||'.'||g_fstgPKNameKey||' '||g_factPKNameKey||',';
g_insert_stmt_row:=g_insert_stmt_row||g_fact_mapping_columns(i)||',';
g_insert_stmt_ctas:=g_insert_stmt_ctas||g_fstg_mapping_columns(i)||' '||g_fact_mapping_columns(i)||',';
g_insert_stmt_row:=substr(g_insert_stmt_row,1,length(g_insert_stmt_row)-1);
g_insert_stmt_ctas:=substr(g_insert_stmt_ctas,1,length(g_insert_stmt_ctas)-1);
g_insert_stmt_row:=g_insert_stmt_row||','||g_factFKName(i)||' ';
g_insert_stmt_ctas:=g_insert_stmt_ctas||','||g_surr_table||'.'||g_fstgActualFKName(i)||' '||
g_factFKName(i)||' ';
g_insert_stmt_ctas:=g_insert_stmt_ctas||','||g_fstg_fk_load_value(i)||' '||g_factFKName(i)||' ';
g_insert_stmt_ctas:=g_insert_stmt_ctas||','||g_fstgTableName||'.'||g_fstgActualFKName(i)||' '||
g_factFKName(i)||' ';
g_insert_stmt_row:=g_insert_stmt_row||',SYSDATE ';
g_insert_stmt_ctas:=g_insert_stmt_ctas||',SYSDATE CREATION_DATE ';
if g_last_update_date_flag then
l_stmt:=l_stmt||',SYSDATE ';
g_insert_stmt_row:=g_insert_stmt_row||',SYSDATE ';
g_insert_stmt_ctas:=g_insert_stmt_ctas||',SYSDATE LAST_UPDATE_DATE ';
g_insert_stmt_ctas:=g_insert_stmt_ctas||' from '||g_surr_table||',';
g_insert_stmt_ctas:=g_insert_stmt_ctas||g_stg_copy_table||' '||g_fstgTableName;
g_insert_stmt_ctas:=g_insert_stmt_ctas||' where '||g_surr_table||'.operation_code=0 and '||
g_fstgTableName||'.rowid='||g_surr_table||'.row_id_copy';
g_insert_stmt_ctas:=g_insert_stmt_ctas||g_user_measure_table||' '||g_fstgTableName;
g_insert_stmt_ctas:=g_insert_stmt_ctas||' where '||g_surr_table||'.operation_code=0 and '||
g_fstgTableName||'.rowid='||g_surr_table||'.row_id_copy';
g_insert_stmt_ctas:=g_insert_stmt_ctas||g_fstgTableName;
g_insert_stmt_ctas:=g_insert_stmt_ctas||' where '||g_surr_table||'.operation_code=0 and '||
g_fstgTableName||'.rowid='||g_surr_table||'.row_id';
g_insert_stmt_row:=g_insert_stmt_row||' from '||g_insert_ctas_table||' where rowid=:a';
g_insert_stmt_ctas:=g_insert_stmt_ctas||' group by ';
g_insert_stmt_ctas:=g_insert_stmt_ctas||g_fstgTableName||'.'||g_groupby_cols(i)||',';
g_insert_stmt_ctas:=g_insert_stmt_ctas||g_surr_table||'.'||g_fstgActualFKName(i)||',';
g_insert_stmt_ctas:=g_insert_stmt_ctas||g_fstgTableName||'.'||g_fstgActualFKName(i)||',';
g_insert_stmt_ctas:=substr(g_insert_stmt_ctas,1,length(g_insert_stmt_ctas)-1);
g_insert_stmt:=l_stmt;
write_to_log_file_n('Insert stmt '||g_insert_stmt);
write_to_log_file_n('Insert stmt row-by-row '||g_insert_stmt_row);
g_audit_net_insert_stmt_row:='insert into '||g_factTableName||'( ';
l_stmt:='insert into '||g_factTableName||'( ';
l_stmt:=' insert /*+PARALLEL ('||g_factTableName||','||g_parallel||') */ into '||
g_factTableName||' ( ';
l_stmt:=' insert into '||g_factTableName||'( ';
g_audit_net_insert_stmt_row:=g_audit_net_insert_stmt_row||g_fact_mapping_columns(i)||',';
g_audit_net_insert_stmt_row:=substr(g_audit_net_insert_stmt_row,1,length(g_audit_net_insert_stmt_row)-1);
g_audit_net_insert_stmt_row:=g_audit_net_insert_stmt_row||','||g_factFKName(i)||' ';
g_audit_net_insert_stmt_row:=g_audit_net_insert_stmt_row||',CREATION_DATE ';
if g_last_update_date_flag then
l_stmt:=l_stmt||',LAST_UPDATE_DATE ';
g_audit_net_insert_stmt_row:=g_audit_net_insert_stmt_row||',LAST_UPDATE_DATE ';
l_stmt:=l_stmt||' ) select ';
g_audit_net_insert_stmt_row:=g_audit_net_insert_stmt_row||') select ';
g_audit_net_insert_stmt_row:=g_audit_net_insert_stmt_row||g_fact_mapping_columns(i)||',';
g_audit_net_insert_stmt_row:=substr(g_audit_net_insert_stmt_row,1,length(g_audit_net_insert_stmt_row)-1);
g_audit_net_insert_stmt_row:=g_audit_net_insert_stmt_row||','||g_factFKName(i)||' ';
g_audit_net_insert_stmt_row:=g_audit_net_insert_stmt_row||',SYSDATE ';
if g_last_update_date_flag then
l_stmt:=l_stmt||',SYSDATE ';
g_audit_net_insert_stmt_row:=g_audit_net_insert_stmt_row||',SYSDATE ';
g_audit_net_insert_stmt_row:=g_audit_net_insert_stmt_row||' from '||g_fact_audit_net_table||
' where rowid=:a';
g_audit_net_insert_stmt:=l_stmt;
write_to_log_file_n('g_audit_net_insert_stmt '||g_audit_net_insert_stmt);
write_to_log_file_n('g_audit_net_insert_stmt_row '||g_audit_net_insert_stmt_row);
if g_update_type='DELETE-INSERT' then
--g_update_stmt:='insert /*+APPEND*/ into '||g_factTableName||'( ';
g_update_stmt:='insert into '||g_factTableName||'( ';
l_stmt:=' insert /*+PARALLEL ('||g_factTableName||','||g_parallel||') */ into '||
g_factTableName||' ( ';
l_stmt:=' insert into '||g_factTableName||'( ';
g_update_stmt:=g_update_stmt||g_fact_mapping_columns(i)||',';
g_update_stmt:=g_update_stmt||g_factFKName(i)||',';
g_update_stmt:=g_update_stmt||'CREATION_DATE,';
if g_last_update_date_flag then
g_update_stmt:=g_update_stmt||'LAST_UPDATE_DATE,';
g_update_stmt:=substr(g_update_stmt,1,length(g_update_stmt)-1);
g_update_stmt:=g_update_stmt||' ) select ';
g_update_stmt:=g_update_stmt||g_fact_mapping_columns(i)||',';
g_update_stmt:=g_update_stmt||g_factFKName(i)||',';
g_update_stmt:=g_update_stmt||g_hold_table||'.CREATION_DATE,';
if g_last_update_date_flag then
g_update_stmt:=g_update_stmt||'SYSDATE,';
g_update_stmt:=substr(g_update_stmt,1,length(g_update_stmt)-1);
g_update_stmt:=g_update_stmt||' from '||g_hold_table;
g_update_stmt_row:=' update '||g_factTableName||' set ( ';
if g_update_type='ROW-BY-ROW' then
g_update_stmt:=' update '||g_factTableName||' set ( ';
elsif g_update_type='MASS' then
g_update_stmt:=' update /*+ ORDERED USE_NL('||g_factTableName||')*/ ';
g_update_stmt:=g_update_stmt||' /*+PARALLEL ('||g_factTableName||','||g_parallel||') */ ';
g_update_stmt:=g_update_stmt||g_factTableName||' set ( ';
g_update_stmt:=g_update_stmt||g_fact_mapping_columns(i)||',';
g_update_stmt_row:=g_update_stmt_row||g_fact_mapping_columns(i)||',';
g_update_stmt:=g_update_stmt||g_factFKName(i)||',';
g_update_stmt_row:=g_update_stmt_row||g_factFKName(i)||',';
if g_last_update_date_flag then
g_update_stmt:=g_update_stmt||' LAST_UPDATE_DATE ';
g_update_stmt_row:=g_update_stmt_row||' LAST_UPDATE_DATE ';
g_update_stmt:=substr(g_update_stmt,1,length(g_update_stmt)-1);
g_update_stmt_row:=substr(g_update_stmt_row,1,length(g_update_stmt_row)-1);
g_update_stmt:=g_update_stmt||')=( select ';
g_update_stmt_row:=g_update_stmt_row||')=( select ';
g_update_stmt:=g_update_stmt||g_fact_mapping_columns(i)||',';
g_update_stmt_row:=g_update_stmt_row||g_fact_mapping_columns(i)||',';
g_update_stmt:=g_update_stmt||g_factFKName(i)||',';
g_update_stmt_row:=g_update_stmt_row||g_factFKName(i)||',';
if g_last_update_date_flag then
g_update_stmt:=g_update_stmt||' SYSDATE ';
g_update_stmt_row:=g_update_stmt_row||' SYSDATE ';
g_update_stmt:=substr(g_update_stmt,1,length(g_update_stmt)-1);
g_update_stmt_row:=substr(g_update_stmt_row,1,length(g_update_stmt_row)-1);
g_update_stmt_row:=g_update_stmt_row||' from '||g_hold_table||' where '||g_hold_table||'.row_id1=:a) where '||
g_factTableName||'.rowid=:b';
if g_update_type='ROW-BY-ROW' then
g_update_stmt:=g_update_stmt||' from '||g_hold_table||' where '||g_hold_table||'.row_id1=:a) where '||
g_factTableName||'.rowid=:b';
elsif g_update_type='MASS' then
g_update_stmt:=g_update_stmt||' from '||g_hold_table||' where '||g_factTableName||'.rowid='||
g_hold_table||'.row_id1) where '||g_factTableName||'.rowid in (select row_id1 from '||g_hold_table||')';
write_to_debug_n('Update stmt '||g_update_stmt);
write_to_debug_n('Update stmt for ROW_BY_ROW is '||g_update_stmt_row);
if g_update_type='ROW-BY-ROW' then
g_delete_stmt :='delete '||g_factTableName||' where rowid=:a ';
elsif g_update_type='MASS' or g_update_type='DELETE-INSERT' then
g_delete_stmt :='delete /*+ ORDERED USE_NL('||g_factTableName||')*/ '||
g_factTableName||' where rowid in (select row_id1 from '||g_surr_table||' where operation_code=2 ';
g_delete_stmt :=g_delete_stmt||'and row_id1<>''I'')';
g_delete_stmt :=g_delete_stmt||')';
write_to_debug_n('Delete stmt '||g_delete_stmt);
set the insert and update flags
*/
procedure set_execute_flags is
Begin
g_insert_flag:=false;
g_update_flag:=false;
g_insert_flag:=true;
write_to_log_file_n('Insert Needed');
g_insert_flag:=false;
write_to_log_file_n('NO Insert Needed');
g_update_flag:=true;
write_to_log_file_n('Update Needed');
g_update_flag:=false;
write_to_log_file_n('NO Update Needed');
g_delete_flag:=true;
write_to_log_file_n('Delete Needed');
g_delete_flag:=false;
write_to_log_file_n('NO Delete Needed');
g_update_type||''','''||l_low_system_mem||''','''||l_rollback||''','''||l_status_table(l_number_jobs)||''');');
argument13=>g_update_type,
argument14=>l_low_system_mem,
argument15=>l_rollback,
argument16=>l_status_table(l_number_jobs));
g_update_type||''','''||l_low_system_mem||''','''||l_rollback||''','''||l_status_table(l_number_jobs)||''');');
g_update_type,
l_low_system_mem,
l_rollback,
l_status_table(l_number_jobs)
);
g_stmt:='select status,count from '||l_status_table(i);
if EDW_OWB_COLLECTION_UTIL.delete_conc_program(
l_temp_conc_short_name,
l_temp_exe_name,
l_bis_short_name,
'SHORT')=false then
null;
p_update_type varchar2,
p_low_system_mem varchar2,
p_rollback varchar2,
p_status_table varchar2
) is
Begin
retcode:='0';
p_update_type,
p_low_system_mem,
p_rollback,
p_status_table);
p_update_type varchar2,
p_low_system_mem varchar2,
p_rollback varchar2,
p_status_table varchar2
) is
l_file_name varchar2(200);
g_stmt:=g_stmt||' as select row_id from '||p_dup_hold_table||' where row_num between '||p_low_end||' and '||
p_high_end;
p_update_type,
g_low_system_mem,
p_fstgTableName,
l_dup_hold_table,
g_rollback);
g_update_type,
g_low_system_mem,
g_fstgTableName,
g_dup_hold_table,
g_rollback);
p_update_type varchar2,
p_low_system_mem boolean,
p_fstgTableName varchar2,
p_dup_hold_table varchar2,
p_rollback varchar2
) return number is
l_stmt varchar2(5000);
l_update_type varchar2(400);
l_update_type:=p_update_type;
l_update_type:='ROW-BY-ROW';
if l_update_type='MASS' or l_update_type='DELETE-INSERT' then
l_stmt:='update /*+ORDERED USE_NL('||p_fstgTableName||')*/ ';
l_stmt:=l_stmt||p_fstgTableName||' set collection_status=:a where rowid in (select row_id from '||
p_dup_hold_table||') ';
elsif l_update_type='ROW-BY-ROW' then
l_stmt:='update '||p_fstgTableName||' set collection_status=:b where rowid=:a';
l_stmt1:='select row_id from '||p_dup_hold_table;
write_to_log_file('Updated '||l_total_count||' rows in '||p_fstgTableName||' to '||l_status||get_time);
function execute_surr_insert return boolean is
Begin
if g_debug then
write_to_log_file_n('In execute_surr_insert'||get_time);
if g_use_mti=false then --if true, insert_fm_ff_table will populate g_user_fk_table
if g_user_fk_table <> g_fstgTableName then
if create_user_fk_table=false then
return false;
execute immediate g_surrogate_stmt; --this is create table as select
set_execute_flags; --is there insert and or update
to boost the performance of the updates
*/
function create_hd_table(p_count number) return boolean is
l_stmt varchar2(32000);
if g_smart_update then
if EDW_OWB_COLLECTION_UTIL.drop_table(g_hold_table_temp) = false then
null;
write_to_log_file_n('Going to execute g_hd_insert_stmt'||get_time);
execute immediate g_hd_insert_stmt;
write_to_log_file('Problem stmt '||g_hd_insert_stmt);
if g_smart_update then
l_number_itemset_cols:=g_number_smart_update_cols;
l_itemset_cols:=g_smart_update_cols;
if l_cols(i)<>'LAST_UPDATE_DATE' and l_cols(i)<>'CREATION_DATE' and l_cols(i)<>'ROW_ID1' then
l_cols_flag(i):=true;
if l_cols(i)<>'LAST_UPDATE_DATE' and l_cols(i)<>'CREATION_DATE' and l_cols(i)<>'ROW_ID1' then
l_cols_flag(i):=true;
l_stmt:=l_stmt||' as select /*+ordered use_nl('||g_factTableName||')*/ ';
l_stmt:=l_stmt||' as select /*+ordered*/ ';
if g_smart_update then
if EDW_OWB_COLLECTION_UTIL.drop_table(g_hold_table_temp) = false then
null;
l_stmt:=l_stmt||' as select row_id1 row_id from '||g_hold_table;
function execute_update_stmt return number is
l_stmt varchar2(10000);
l_total_update number:=0;
l_update_type varchar2(400);
write_to_log_file_n('In execute_update_stmt'||get_time);
l_update_type:=g_update_type;
l_update_type:='ROW-BY-ROW';
<>
if l_update_type='ROW-BY-ROW' then
l_stmt:='create table '||l_hold_table||' tablespace '||g_op_table_space;
l_stmt:=l_stmt||' as select row_id1 from '||g_hold_table;
l_stmt:='select row_id1 from '||l_hold_table;
execute immediate g_update_stmt_row using l_rowid(i),l_rowid(i);
l_total_update:=l_total_update+l_count;
execute immediate g_update_stmt_row using l_rowid(i),l_rowid(i);
l_total_update:=l_total_update+l_count;
elsif l_update_type='MASS' then
begin
EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
execute immediate g_update_stmt;
l_total_update:=sql%rowcount;
write_to_log_file_n('Memory issue with Mass Update. Retrying using ROW_BY_ROW');
l_update_type:='ROW-BY-ROW';
goto start_update;
goto start_update;
write_to_log_file('Problem stmt '||g_update_stmt);
elsif l_update_type='DELETE-INSERT' then
--first delete
l_stmt:='delete '||g_factTableName||' where exists (select 1 from '||g_hold_table||' where '||
g_hold_table||'.row_id1='||g_factTableName||'.rowid)';
write_to_log_file_n('Deleted '||sql%rowcount||' rows'||get_time);
write_to_log_file_n('Going to execute g_update_stmt'||get_time);
execute immediate g_update_stmt;
l_total_update:=sql%rowcount;
write_to_log_file('Problem stmt '||g_update_stmt);
write_to_log_file_n('Updated '||l_total_update||' records'||get_time);
return l_total_update;
function execute_delete_stmt return number is
l_stmt varchar2(10000);
l_total_delete number:=0;
write_to_log_file_n('In execute_delete_stmt'||get_time);
if g_update_type='ROW-BY-ROW' then
l_stmt:='select row_id1 from '||g_surr_table||' where operation_code=2';
execute immediate g_delete_stmt using l_rowid(i);
l_total_delete:=l_total_delete+l_count;
execute immediate g_delete_stmt using l_rowid(i);
l_total_delete:=l_total_delete+l_count;
elsif g_update_type='MASS' or g_update_type='DELETE-INSERT' then
begin
--create a rowid table here and use that for deletes
EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
execute immediate g_delete_stmt;
l_total_delete:=sql%rowcount;
write_to_log_file('Problem stmt '||g_delete_stmt);
write_to_log_file_n('Deleted '||l_total_delete||' records'||get_time);
return l_total_delete;
function execute_fa_nc_insert(p_flag varchar2) return boolean is
Begin
if p_flag='INSERT' then
--here, if there is fact audit or net change, insert into those tables
if g_fact_audit then
if insert_fa_fact_insert=false then
return false;
if insert_nc_fact_insert=false then
return false;
if p_flag='UPDATE' then
if g_fact_audit then
if insert_fa_fact_update = false then
return false;
if insert_nc_fact_update = false then
return false;
function execute_insert_update_delete(p_count number) return boolean is
l_count number;
write_to_log_file_n('In execute_insert_update_delete'||get_time);
if g_insert_flag then
if execute_insert_stmt(p_count)=false then
return false;
if g_update_flag and g_skip_update=false then
--create the hold table on the fly insert into it all the values
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Create Hold Table',sysdate,
null,'MAPPING','CREATE-TABLE',g_jobid_stmt||'CHT'||p_count,'I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'CHT'||p_count,'U');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'CHT'||p_count,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Move Update data into dlog ',sysdate,
null,'MAPPING','INSERT',g_jobid_stmt||'MUDL'||p_count,'I');
if excecute_data_into_dlog('UPDATE') = false then
write_to_log_file_n('excecute_data_into_dlog returned with error');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'MUDL'||p_count,'U');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'MUDL'||p_count,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Process Audit or Netchange Records',sysdate,
null,'MAPPING','UPDATE',g_jobid_stmt||'PANU'||p_count,'I');
if execute_fa_nc_insert('UPDATE') = false then
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'PANU'||p_count,'U');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'PANU'||p_count,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Update '||g_primary_target_name,sysdate,
null,'MAPPING','UPDATE',g_jobid_stmt||'UPDATE'||p_count,'I');
l_count:=execute_update_stmt;
g_total_update:=l_count;
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'UPDATE'||p_count,'U');
g_total_update:=0;
write_to_log_file_n('No change to update');
if g_delete_flag and g_skip_delete=false then
if g_mapping_type='FACT' then
if g_is_source=true or g_is_custom_source=true then
if g_is_delete_trigger_imp=false then
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Move Delete data into dlog ',sysdate,
null,'MAPPING','INSERT',g_jobid_stmt||'MDDDL'||p_count,'I');
if excecute_data_into_dlog('DELETE') = false then
write_to_log_file_n('excecute_data_into_dlog returned with error');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'MDDDL'||p_count,'U');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'MDDDL'||p_count,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Delete '||g_primary_target_name,sysdate,
null,'MAPPING','DELETE',g_jobid_stmt||'DELETE'||p_count,'I');
l_count:=execute_delete_stmt;
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'DELETE'||p_count,'U');
g_total_delete:=l_count;
how is deletes to be handled in fact audit and net change?
*/
return true;
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Create User Measure FK Table',
sysdate,null,'MAPPING','CREATE-TABLE',g_jobid_stmt||'CRUSMESFK'||p_count,'I');
if insert_fm_ff_table=false then
g_status:=false;
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'CRUSMESFK'||p_count,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Create OP Code Table',sysdate,null,
'MAPPING','CREATE-TABLE',g_jobid_stmt||'CROPTAB'||p_count,'I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'CROPTAB'||p_count,'U');
make_hd_insert_stmt;
make_insert_update_stmt;
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Create FK Key Table',sysdate,null,
'MAPPING','CREATE-TABLE',g_jobid_stmt||'CRFKT'||p_count,'I');
if execute_surr_insert=false then
write_to_log_file_n('execute_surr_insert returned with error');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'CRFKT'||p_count,'U');
if execute_insert_update_delete(p_count) = false then
g_status:=false;
write_to_log_file_n('execute_insert_update_delete returned with error');
''''||g_update_type||''','||
''''||l_low_system_mem||''','||
''''||g_op_table_space||''','||
''||nvl(g_parallel,0)||','||
''||nvl(g_sort_area_size,0)||','||
''||nvl(g_hash_area_size,0)||','||
''''||nvl(g_rollback,'null')||''','||
''''||l_version_GT_1159||''','||
''''||p_table||''');');
''''||g_update_type||''','||
''''||l_low_system_mem||''','||
''''||g_op_table_space||''','||
''||nvl(g_parallel,0)||','||
''||nvl(g_sort_area_size,0)||','||
''||nvl(g_hash_area_size,0)||','||
''''||nvl(g_rollback,'null')||''','||
''''||l_version_GT_1159||''','||
''''||p_table||''');');
p_update_type varchar2,
p_low_system_mem varchar2,
p_op_table_space varchar2,
p_parallel number,
p_sort_area_size number,
p_hash_area_size number,
p_rollback varchar2,
p_version_GT_1159 varchar2,
p_table varchar2 --this is the status table
) is
---
l_stmt varchar2(3000);
g_update_type:=p_update_type;
execute immediate 'insert into '||p_table||'(status,message) values(:1,:2)' using
'ERROR',g_status_message;
execute immediate 'insert into '||p_table||'(status,message) values(:1,:2)' using
'SUCCESSS','SUCCESS';
execute immediate 'insert into '||p_table||'(status,message) values(:1,:2)' using
'ERROR',sqlerrm;
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Create Error Rowid Table',sysdate,null,
'MAPPING','CREATE-TABLE',g_jobid_stmt||'CRERT'||p_count,'I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'CRERT'||p_count,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Log dangling keys',sysdate,null,
'MAPPING','CREATE-TABLE',g_jobid_stmt||'LDKD'||p_count,'I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'LDKD'||p_count,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Mark DANGLING in Interfact Table',sysdate,null,
'MAPPING','UPDATE',g_jobid_stmt||'MKDNG'||p_count,'I');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'MKDNG'||p_count,'U');
l_update_type varchar2(400);
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Mark Records as COLLECTED',sysdate,null,
'MAPPING','UPDATE',g_jobid_stmt||'MKRC'||p_count,'I');
if update_stg_status_column(g_surr_table,'row_id',null,'COLLECTED',true)=false then
return false;
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'MKRC'||p_count,'U');
function update_stg_status_column(
p_src_table varchar2,
p_rowid_col varchar2,
p_where_stmt varchar2,
p_status varchar2, --COLLECTED OR READY
p_create_iot boolean
) return boolean is
---
l_stmt varchar2(4000);
l_update_type varchar2(400);
write_to_log_file_n('In update_stg_status_column'||get_time);
l_update_type:=g_update_type;
l_update_type:='ROW-BY-ROW';
if l_update_type='MASS' or l_update_type='DELETE-INSERT' then
if p_create_iot then
l_surr_rowid_table:=p_src_table||'R';
l_stmt:=l_stmt||' as select '||p_rowid_col||' from '||p_src_table||' '||p_where_stmt;
l_stmt:='update /*+ORDERED USE_NL('||g_fstgTableName||')*/ ';
'(select '||p_rowid_col||' from '||l_surr_rowid_table||')';
write_to_log_file_n('Updated '||sql%rowcount||' records to '''||p_status||''''||get_time);
elsif l_update_type='ROW-BY-ROW' then
l_stmt:='update '||g_fstgTableName||' set collection_status='''||p_status||''' where rowid=:a';
l_stmt1:='select '||p_rowid_col||' row_id from '||p_src_table||' '||p_where_stmt;
write_to_log_file_n('Updated '||l_total_count||' records to '''||p_status||''''||get_time);
write_to_log_file_n('Error in update_stg_status_column '||sqlerrm||get_time);
l_stmt:='select max(round) from '||g_fact_dlog;
l_stmt:='create table '||g_update_dlog_lookup_table||' tablespace '||g_op_table_space;
l_stmt:=l_stmt||' as select ';
if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_dlog_lookup_table)=false then
null;
write_to_log_file('Created '||g_update_dlog_lookup_table||' with '||sql%rowcount||' rows '||get_time);
EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_update_dlog_lookup_table,instr(g_update_dlog_lookup_table,'.')+1,
length(g_update_dlog_lookup_table)),substr(g_update_dlog_lookup_table,1,instr(g_update_dlog_lookup_table,'.')-1));
if this is a source for derived facts, then we need to move the FACT data into the dlog table before update
*/
procedure make_data_into_dlog_stmt is
l_stmt varchar2(4000);
g_fact_delete_dlog_stmt:=null;
write_to_log_file_n('No delete log');
write_to_log_file_n('No columns found for delete log '||g_fact_dlog);
g_fact_dlog_stmt:='insert into '||g_fact_dlog||'(';
g_fact_dlog_stmt:='insert /*+PARALLEL ('||g_fact_dlog||','||g_parallel||')*/ into '||g_fact_dlog||'(';
if upper(g_dlog_columns(i)) <> 'ROW_ID' and upper(g_dlog_columns(i)) <> 'DLOG_LAST_UPDATE_DATE'
and upper(g_dlog_columns(i)) <> 'PK_KEY' and upper(g_dlog_columns(i)) <> 'ROUND' then
g_fact_dlog_stmt:=g_fact_dlog_stmt||g_dlog_columns(i)||',';
if EDW_OWB_COLLECTION_UTIL.value_in_table(g_dlog_columns,g_number_dlog_columns,'DLOG_LAST_UPDATE_DATE')=true then
g_fact_dlog_stmt:=g_fact_dlog_stmt||',DLOG_LAST_UPDATE_DATE ';
g_fact_dlog_stmt:=g_fact_dlog_stmt||') select /*+ORDERED USE_NL('||g_object_name||')*/ ';
if upper(g_dlog_columns(i)) <> 'ROW_ID' and upper(g_dlog_columns(i)) <> 'DLOG_LAST_UPDATE_DATE'
and upper(g_dlog_columns(i)) <> 'PK_KEY' and upper(g_dlog_columns(i)) <> 'ROUND' then
g_fact_dlog_stmt:=g_fact_dlog_stmt||g_object_name||'.'||g_dlog_columns(i)||',';
if EDW_OWB_COLLECTION_UTIL.value_in_table(g_dlog_columns,g_number_dlog_columns,'DLOG_LAST_UPDATE_DATE')=true then
g_fact_dlog_stmt:=g_fact_dlog_stmt||',SYSDATE ';
g_fact_delete_dlog_stmt:='insert into '||g_fact_dlog||'(';
g_fact_delete_dlog_stmt:='insert /*+PARALLEL ('||g_fact_dlog||','||g_parallel||')*/ into '||g_fact_dlog||'(';
if upper(g_dlog_columns(i)) <> 'ROW_ID' and upper(g_dlog_columns(i)) <> 'DLOG_LAST_UPDATE_DATE'
and upper(g_dlog_columns(i)) <> 'PK_KEY' and upper(g_dlog_columns(i)) <> 'ROUND' then
g_fact_delete_dlog_stmt:=g_fact_delete_dlog_stmt||g_dlog_columns(i)||',';
g_fact_delete_dlog_stmt:=g_fact_delete_dlog_stmt||'row_id,pk_key,round ';
if EDW_OWB_COLLECTION_UTIL.value_in_table(g_dlog_columns,g_number_dlog_columns,'DLOG_LAST_UPDATE_DATE')=true then
g_fact_delete_dlog_stmt:=g_fact_delete_dlog_stmt||',DLOG_LAST_UPDATE_DATE ';
g_fact_delete_dlog_stmt:=g_fact_delete_dlog_stmt||') select /*+ORDERED USE_NL('||g_object_name||')*/ ';
g_fact_delete_dlog_stmt:=g_fact_delete_dlog_stmt||' /*+PARALLEL('||g_object_name||','||g_parallel||')*/ ';
if upper(g_dlog_columns(i)) <> 'ROW_ID' and upper(g_dlog_columns(i)) <> 'DLOG_LAST_UPDATE_DATE'
and upper(g_dlog_columns(i)) <> 'PK_KEY' and upper(g_dlog_columns(i)) <> 'ROUND' then
g_fact_delete_dlog_stmt:=g_fact_delete_dlog_stmt||g_object_name||'.'||g_dlog_columns(i)||',';
g_fact_delete_dlog_stmt:=g_fact_delete_dlog_stmt||g_object_name||'.rowid,'||g_object_name||'.'||g_factPKNameKey||
','||g_dlog_rowid_table||'.round';
if EDW_OWB_COLLECTION_UTIL.value_in_table(g_dlog_columns,g_number_dlog_columns,'DLOG_LAST_UPDATE_DATE')=true then
g_fact_delete_dlog_stmt:=g_fact_delete_dlog_stmt||',SYSDATE ';
g_fact_delete_dlog_stmt:=g_fact_delete_dlog_stmt||' from '||g_dlog_rowid_table||','||g_object_name;
g_fact_delete_dlog_stmt:=g_fact_delete_dlog_stmt||' where '||
g_dlog_rowid_table||'.row_id='||g_object_name||'.rowid';
write_to_log_file_n('g_fact_delete_dlog_stmt is '||g_fact_delete_dlog_stmt);
l_update_dlog_rowid_table varchar2(400);
if p_mode='UPDATE' then
l_stmt:=l_stmt||' as select row_id1 row_id from '||g_hold_table;
l_stmt:=l_stmt||' as select row_id1 row_id from '||g_surr_table||' where operation_code=2';
l_stmt:=l_stmt||' as select distinct row_id from '||g_update_dlog_lookup_table;
l_stmt:=l_stmt||' as select A.row_id,decode(B.rowid,null,0,'||g_max_round||') round from '||
l_dlog_rowid_table||' A,'||l_distinct_table||' B where A.row_id=B.row_id(+)';
if p_mode='UPDATE' then
l_stmt:=l_stmt||' as select row_id1 row_id,0 round from '||g_hold_table;
l_stmt:=l_stmt||' as select row_id1 row_id,0 round from '||g_surr_table||' where operation_code=2';
function insert_dlog_table(p_mode varchar2) return boolean is
l_stmt varchar2(32000);
write_to_log_file_n('In insert_dlog_table , p_mode='||p_mode);
if p_mode='UPDATE' then
l_stmt:=g_fact_dlog_stmt;
elsif p_mode='DELETE' then
l_stmt:=g_fact_delete_dlog_stmt;
write_to_log_file_n('There is no delete log for this fact.');
if this is a source for derived facts, then we need to move the FACT data into the dlog table before update
*/
function excecute_data_into_dlog(p_mode varchar2) return boolean is
l_stmt varchar2(32000);
if insert_dlog_table(p_mode)=false then
return false;
select fact_item.column_name from
edw_attribute_sets_md_v sis,
edw_attribute_set_columns_md_v isu,
edw_pvt_columns_md_v fact_item
where sis.entity_id=p_fact_id
and sis.attribute_group_name=p_is_name
and isu.attribute_group_id=sis.attribute_group_id
and fact_item.column_id=isu.column_id
and fact_item.parent_object_id=p_fact_id;
procedure select_fact_audit is
--selects the rowids to audit
Begin
write_to_debug_n('In select_fact_audit '||get_time);
select fact_item.column_name from
edw_attribute_sets_md_v sis,
edw_attribute_set_columns_md_v isu,
edw_pvt_columns_md_v fact_item
where sis.entity_id=p_fact_id
and sis.attribute_group_name=p_is_name
and isu.attribute_group_id=sis.attribute_group_id
and fact_item.column_id=isu.column_id
and fact_item.parent_object_id=p_fact_id;
procedure select_net_change is
l_count number:=0;
write_to_debug('In select_net_change '||get_time);
g_update_dlog_lookup_table:=g_bis_owner||'.'||l_primary_target_name||'SDL';
g_insert_ctas_table:=g_bis_owner||'.'||l_primary_target_name||'ICT';
g_total_insert:=0;
g_total_update:=0;
g_total_delete:=0;
g_skip_ilog_update:=false;
g_type_ok_generation:='CTAS'; --or UPDATE
g_smart_update_name:='CHECK_COLUMNS_FOR_UPDATE';
g_is_delete_trigger_imp:=EDW_OWB_COLLECTION_UTIL.is_delete_trigger_imp(g_primary_target_name,g_table_owner);
if g_is_delete_trigger_imp then
write_to_log_file_n('Delete trigger implemented');
write_to_log_file_n('Delete trigger NOT implemented');
g_skip_update:=false;
if edw_option.get_warehouse_option(g_object_name,null,'SKIPUPDATE',l_option_value)=false then
null;
if edw_option.get_option_columns(g_object_name,null,'SKIPUPDATE',l_table,l_number_table)=false then
null;
g_skip_update:=true;
g_skip_update:=false;
g_skip_update:=true;
if EDW_OWB_COLLECTION_UTIL.is_itemset_implemented(g_primary_target_name,'SKIP_UPDATE')='Y' then
g_skip_update:=true;
if g_skip_update then
write_to_log_file_n('Skip Update');
g_skip_delete:=false;
if edw_option.get_warehouse_option(g_object_name,null,'SKIPDELETE',l_option_value)=false then
null;
if edw_option.get_option_columns(g_object_name,null,'SKIPDELETE',l_table,l_number_table)=false then
null;
g_skip_delete:=true;
g_skip_delete:=false;
g_skip_delete:=true;
if EDW_OWB_COLLECTION_UTIL.is_itemset_implemented(g_primary_target_name,'SKIP_DELETE')='Y' then
g_skip_delete:=true;
if g_skip_delete then
write_to_log_file_n('Skip Delete');
if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_ctas_table)=false then
null;
l_stmt:='select count(*), operation_code from '||g_surr_table||
' group by operation_code';
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
l_stmt:=l_stmt||' as select ';
' MINUS select row_id from '||g_surr_table;
l_update_type varchar2(400);
if update_stg_status_column(g_error_rowid_table,'row_id',null,'DANGLING',false)=false then
return false;
l_stmt:='select count(*) from '||p_ok_rowid_table||' where status=1';
if EDW_OWB_COLLECTION_UTIL.insert_temp_log_table(
g_object_name,
g_object_type,
g_conc_program_id,
l_ins_instance_name,
l_ins_request_id_table,
l_ins_rows_ready,
l_ins_rows_processed,
l_ins_rows_collected,
l_ins_rows_dangling,
l_ins_rows_duplicate,
l_ins_rows_error,
g_total_records,
g_total_insert,
g_total_update,
g_total_delete,
l_number_ins_req_coll) = false then
g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
if g_type_ok_generation='UPDATE' then
l_stmt:='update '||g_ok_rowid_table||' set status=1 where status=0 ';
write_to_log_file_n('Updated '||g_number_rows_ready||' records from ''READY'' to ''PROCESSING'' '||get_time);
l_stmt:=l_stmt||' as select row_id,decode(status,1,2,2,2,decode(sign(rownum-'||
g_collection_size||'),1,0,1)) status from (select row_id,status from '||g_ok_rowid_table_prev||
' order by status) abc ';
l_stmt:=l_stmt||' as select row_id,decode(status,1,2,0,1,2) status from '||
g_ok_rowid_table_prev;
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
if p_col='LAST_UPDATE_DATE' then
l_stmt:=l_stmt||' lstg.'||g_fstgPKName||' '||g_fstgPKName||',lstg.rowid row_id,'||
' lstg.'||p_col||' col_last_update_date, rownum col_rownum from '||g_dup_hold_pk_table||' aa,'||
g_fstgTableName||' lstg where lstg.'||g_fstgPKName||'=aa.'||g_fstgPKName;
l_stmt:='select 1 from edw_coll_progress_log where object_name=:a and object_type=:b';
l_stmt:=l_stmt||' as select c.row_id ,c.status from '||p_ok_copy_rowid_table||' c,'||
p_ok_rowid_table||' a where c.row_id=a.row_id';
l_stmt:='delete edw_coll_progress_log where object_name=:a and object_type=:b';
l_insert_count number;
l_insert_count:=0;
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' MINUS select row_id , 0 status from '||g_dup_hold_table;
l_stmt:=l_stmt||' as select A.row_id,A.status from '||g_ok_rowid_table||' A,'||g_reqid_table||' B '||
' where A.row_id=B.row_id';
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' MINUS select row_id row_id from '||g_ok_rowid_table;
l_stmt:='insert into '||g_ok_rowid_table||'(row_id, status) ';
l_stmt:=l_stmt||' select ';
l_insert_count:=sql%rowcount;
g_ok_rowid_number:=l_base_count+l_insert_count;
write_to_log_file_n('Inserted '||l_insert_count||' rows into '||g_ok_rowid_table||get_time);
l_stmt:=l_stmt||' as select row_id row_id, status status from '||g_ok_rowid_table||
' UNION ALL select row_id row_id,0 status from '||l_ok_rowid_table;
l_stmt:=l_stmt||' as select row_id from '||l_ok_copy_rowid_table||
' MINUS select row_id from '||g_dup_hold_table;
l_stmt:=l_stmt||' as select c.row_id ,c.status from '||l_ok_copy_rowid_table||' c,'||
l_ok_rowid_table||' a where c.row_id=a.row_id';
if EDW_OWB_COLLECTION_UTIL.insert_into_coll_progress(g_ok_rowid_table,'OK_ROWID',null,null)=false then
null;
l_col varchar2(400);--what col to use. last update date or rownum
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_fstgTableName,'LAST_UPDATE_DATE IS NOT NULL')=2 then
l_col:='LAST_UPDATE_DATE';
l_col_use:='COL_LAST_UPDATE_DATE';
if l_col='LAST_UPDATE_DATE' then
declare
l_res number:=null;
l_stmt:='select 1 from '||g_dup_rownum||' having count(*)>1 group by '||l_col_use||','||g_fstgPKName;
write_to_log_file('Col is last_update_date');
write_to_log_file_n('Last_update_date repeats for same PK. Going to try ROWNUM');
l_stmt:=l_stmt||' as select max('||l_col_use||') col,'||g_fstgPKName||' from '||
g_dup_rownum||' group by '||g_fstgPKName;
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' row_id from '||g_dup_rownum||' MINUS select /*+ORDERED*/ row_id from '||
g_dup_rownum_rowid||','||g_dup_rownum||' where '||g_dup_rownum||'.'||l_col_use||'='||
g_dup_rownum_rowid||'.col and '||g_dup_rownum||'.'||g_fstgPKName||'='||g_dup_rownum_rowid||'.'||g_fstgPKName;
l_stmt:='select count(*) from '||g_dup_hold_table;
l_stmt:=l_stmt||' as select row_id from '||g_ok_rowid_table||' where status=1';
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' as select /*+ORDERED USE_NL('||g_FactTableName||')*/ ';
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
if g_fact_audit or g_fact_net_change or g_update_type='DELETE-INSERT' then
l_stmt:=l_stmt||','||g_FactTableName||'.'||g_factPKNameKey||' '||g_fstgPKNameKey;
if g_update_type='DELETE-INSERT' then
l_stmt:=l_stmt||','||g_FactTableName||'.'||g_factPKName||' '||g_fstgPKName;
l_stmt:=l_stmt||' as select row_id,row_id1,';
if g_update_type='DELETE-INSERT' then
l_stmt:=l_stmt||','||g_fstgPKName||',CREATION_DATE';
g_opcode_stmt:=g_opcode_stmt||' as select ';
'decode ('||l_opcode_table||'.operation_code,''DELETE'',2,0) operation_code, ';
if g_update_type='DELETE-INSERT' then
g_opcode_stmt:=g_opcode_stmt||',0 '||g_fstgPKName;
g_opcode_stmt:=g_opcode_stmt||' as select /*+ORDERED USE_NL('||g_FactTableName||')*/ ';
g_opcode_stmt:=g_opcode_stmt||' as select /*+ORDERED*/ ';
' decode ('||l_opcode_table||'.operation_code,''DELETE'',2,decode('||
g_FactTableName||'.rowid,null,0,1)) operation_code, ';
if g_update_type='DELETE-INSERT' then
g_opcode_stmt:=g_opcode_stmt||','||g_FactTableName||'.'||g_factPKName||' '||g_fstgPKName;
g_opcode_stmt:=g_opcode_stmt||' as select ';
' decode ('||l_opcode_table||'.operation_code,''DELETE'',2,decode('||
g_FactTableName||'.row_id1,null,0,1)) operation_code, ';
if g_update_type='DELETE-INSERT' then
g_opcode_stmt:=g_opcode_stmt||','||g_FactTableName||'.'||g_fstgPKName||' '||g_fstgPKName;
l_stmt:=l_stmt||' as select row_id from '||g_ok_rowid_table||' where status=1';
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
l_stmt:=l_stmt||' as select A.row_id,A.'||g_factPKName||' '||g_factPKName||'1, '||
'decode(A.'||g_factPKNameKey||',null,B.pk_key,A.'||g_factPKNameKey||') '||g_factPKNameKey||' ,'||
'A.row_id2 from '||l_table11||' A,'||l_table_pp||' B where A.row_id=B.row_id(+)';
l_stmt:=l_stmt||' as select row_id,'||g_factPKName||'1,'||
'decode('||g_factPKNameKey||',null,'||l_next_pk||','||g_factPKNameKey||') '||g_factPKNameKey||
',row_id2 from '||l_table1;
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' as select /*+ORDERED USE_NL('||g_FactTableName||')*/ ';
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
' decode ('||l_opcode_pk_table||'.operation_code,''DELETE'',2,decode('||
g_FactTableName||'.rowid,null,0,1)) operation_code, ';
if g_update_type='DELETE-INSERT' then
l_stmt:=l_stmt||','||g_FactTableName||'.'||g_factPKName||' '||g_fstgPKName;
function update_ok_status_2 return boolean is
l_stmt varchar2(2000);
l_stmt:='update '||g_ok_rowid_table||' set status=2 where status=1';
write_to_log_file_n('Updated '||sql%rowcount||' rows from status 1 to 2'||get_time);
l_stmt:='select operation,options, object_name,cardinality from '||g_plan_table;
l_opcode_update_table varchar2(200);
l_opcode_update_table:=g_opcode_table||'U';
l_stmt:='create table '||l_opcode_update_table||' tablespace '||g_op_table_space;
l_stmt:=l_stmt||' as select row_id, row_id1 from '||g_opcode_table||' where operation_code=1';
if EDW_OWB_COLLECTION_UTIL.drop_table(l_opcode_update_table)=false then
null;
l_stmt:=l_stmt||' as select /*+ORDERED USE_NL('||g_fstgTableName||')*/ ';
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
l_stmt:=l_stmt||' as select /*+ORDERED ';
l_stmt:=l_stmt||' as select row_id ';
l_opcode_update_table -- to join to
*/
declare
l_table1 varchar2(200);
l_stmt:=l_stmt||' as select b.row_id1,a.'||g_fstgActualFKName(i)||' from '||
l_opcode_update_table||' b,'||g_surr_tables(g_number_surr_tables)||' a where a.row_id=b.row_id';
l_stmt:='select /*+ordered ';
if insert_into_parent_fk_log(l_dang_instance,l_number_dang_instance,
g_fstgUserFKName(i),g_dimTableId(i),g_dimTableName(i),g_dim_auto_dang_table(i),
g_dim_lowest_ltc_id(i))=false then
return false;
l_stmt:='insert into '||g_surr_tables(g_number_surr_tables)||'(row_id';
l_stmt:=l_stmt||') select row_id';
write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);
if EDW_OWB_COLLECTION_UTIL.drop_table(l_opcode_update_table)=false then
null;
l_stmt:=l_stmt||' as select ';
if g_update_type='DELETE-INSERT' then
l_stmt:=l_stmt||','||g_opcode_table||'.'||g_fstgPKName||' '||g_fstgPKName||','||
g_opcode_table||'.CREATION_DATE CREATION_DATE';
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' as select ';
if g_update_type='DELETE-INSERT' then
l_stmt:=l_stmt||','||g_opcode_table||'.'||g_fstgPKName||' '||g_fstgPKName||','||
g_opcode_table||'.CREATION_DATE CREATION_DATE';
l_stmt:=l_stmt||' as select row_id from '||g_opcode_table;
l_stmt:=l_stmt||' as select /*+ORDERED USE_NL('||g_fstgTableName||')*/ ';
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
l_stmt:=l_stmt||' as select ';
l_stmt:='select '||p_fk||' from '||g_fstgTableName||' where rownum=1';
insert into fact audit all new rows of fact
*/
function insert_fa_fact_insert return boolean is
l_stmt varchar2(32000);
write_to_log_file_n('In insert_fa_fact_insert'||get_time);
l_stmt:='insert into '||g_fact_audit_name;
l_stmt:='insert /*+PARALLEL('||g_fact_audit_name||','||g_parallel||')*/ into '||g_fact_audit_name;
if g_last_update_date_flag then
l_stmt:=l_stmt||'LAST_UPDATE_DATE,';
l_stmt:=l_stmt||' select ';
if g_last_update_date_flag then
l_stmt:=l_stmt||'SYSDATE,';
write_to_log_file_n('Inserted '||sql%rowcount||' rows into '||g_fact_audit_name||get_time);
insert into fact audit all update of fact
*/
function insert_fa_fact_update return boolean is
l_stmt varchar2(32000);
for columns that have changed, insert
*/
if g_debug then
write_to_log_file_n('In insert_fa_fact_update'||get_time);
l_stmt:=l_stmt||' as select /*+ORDERED USE_NL('||g_factTableName||')*/ ';
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
l_stmt:=l_stmt||' as select '||g_hold_table||'.rowid row_id from '||g_hold_table||
' MINUS select row_id from '||l_ilog;
l_stmt:='insert into '||g_fact_audit_name;
l_stmt:='insert /*+PARALLEL('||g_fact_audit_name||','||g_parallel||')*/ into '||g_fact_audit_name;
if g_last_update_date_flag then
l_stmt:=l_stmt||'LAST_UPDATE_DATE,';
l_stmt:=l_stmt||' select /*+ORDERED ('||g_hold_table||')*/ ';
if g_last_update_date_flag then
l_stmt:=l_stmt||'SYSDATE,';
write_to_log_file_n('Inserted '||sql%rowcount||' rows into '||g_fact_audit_name||get_time);
insert into fact nc all new rows of fact
*/
function insert_nc_fact_insert return boolean is
l_stmt varchar2(32000);
write_to_log_file_n('In insert_nc_fact_insert'||get_time);
l_stmt:='insert into '||g_fact_net_change_name;
l_stmt:='insert /*+PARALLEL('||g_fact_net_change_name||','||g_parallel||')*/ into '||g_fact_net_change_name;
if g_last_update_date_flag then
l_stmt:=l_stmt||'LAST_UPDATE_DATE,';
l_stmt:=l_stmt||' select ';
if g_last_update_date_flag then
l_stmt:=l_stmt||'SYSDATE,';
write_to_log_file_n('Inserted '||sql%rowcount||' rows into '||g_fact_net_change_name||get_time);
insert into fact net_change all update of fact
*/
function insert_nc_fact_update return boolean is
l_stmt varchar2(32000);
for columns that have changed, insert
*/
if g_debug then
write_to_log_file_n('In insert_nc_fact_update'||get_time);
l_stmt:=l_stmt||' as select /*+ORDERED USE_NL('||g_factTableName||')*/ ';
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
l_stmt:=l_stmt||' as select '||g_hold_table||'.rowid row_id from '||g_hold_table||
' MINUS select row_id from '||l_ilog;
l_stmt:='insert into '||g_fact_net_change_name;
l_stmt:='insert /*+PARALLEL('||g_fact_net_change_name||','||g_parallel||')*/ into '||g_fact_net_change_name;
if g_last_update_date_flag then
l_stmt:=l_stmt||'LAST_UPDATE_DATE,';
l_stmt:=l_stmt||' select /*+ORDERED USE_NL('||g_factTableName||')*/ ';
l_stmt:=l_stmt||' select /*+ORDERED*/ ';
if g_last_update_date_flag then
l_stmt:=l_stmt||'SYSDATE,';
write_to_log_file_n('Inserted '||sql%rowcount||' rows into '||g_fact_net_change_name||get_time);
procedure insert_into_load_progress(p_load_fk number,p_object_name varchar2,p_load_progress varchar2,
p_start_date date,p_end_date date,p_category varchar2, p_operation varchar2,p_seq_id varchar2,p_flag varchar2) is
Begin
EDW_OWB_COLLECTION_UTIL.insert_into_load_progress(p_load_fk,p_object_name,g_primary_target,p_load_progress,
p_start_date,p_end_date,p_category,p_operation,p_seq_id,p_flag,g_primary_target);
procedure insert_into_load_progress_d(p_load_fk number,p_object_name varchar2,p_load_progress varchar2,
p_start_date date,p_end_date date,p_category varchar2, p_operation varchar2,p_seq_id varchar2,p_flag varchar2) is
Begin
if g_debug then
EDW_OWB_COLLECTION_UTIL.insert_into_load_progress(p_load_fk,p_object_name,g_primary_target,p_load_progress,
p_start_date,p_end_date,p_category,p_operation,p_seq_id,p_flag,g_primary_target);
insert_into_load_progress_d(p_load_pk,p_primary_target_name,'Analyze '||p_primary_target_name,sysdate,null,
'MAPPING','ANALYZE','ANFT','I');
insert_into_load_progress_d(p_load_pk,null,null,null,sysdate,null,null,'ANFT','U');
insert_into_load_progress_d(p_load_pk,p_primary_target_name,'Analyze '||p_fact_audit_name,sysdate,null,
'MAPPING','ANALYZE','ANFA','I');
insert_into_load_progress_d(p_load_pk,null,null,null,sysdate,null,null,'ANFA','U');
insert_into_load_progress_d(p_load_pk,p_primary_target_name,'Analyze '||p_fact_net_change_name,sysdate,null,
'MAPPING','ANALYZE','ANFN','I');
insert_into_load_progress_d(p_load_pk,null,null,null,sysdate,null,null,'ANFN','U');
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||g_factPKName||','||g_factPKNameKey||',sysdate CREATION_DATE,sysdate LAST_UPDATE_DATE from '||
g_primary_target_name||' where 1=2';
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' from '||l_da_table_dis||' MINUS select ';
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||g_factPKNameKey||' from '||l_master_da_table_dis1||' UNION ALL select /*+ORDERED*/ ';
l_stmt:='insert ';
l_stmt:=l_stmt||g_factPKName||','||g_factPKNameKey||',CREATION_DATE,LAST_UPDATE_DATE) select ';
write_to_log_file_n('inserted into '||g_da_table||' '||sql%rowcount||' rows '||get_time);
l_stmt:=l_stmt||' as select ';
l_stmt:='insert ';
l_stmt:=l_stmt||'into '||g_pp_table||' '||l_table||' (PK,PK_KEY,LOADED_PK,CREATION_DATE) select PK,PK_KEY,'||
'LOADED_PK,SYSDATE from '||l_pp_table_temp;
write_to_log_file_n('inserted into '||g_pp_table||' '||sql%rowcount||' rows '||get_time);
l_stmt:='insert ';
l_stmt:=l_stmt||g_factPKName||','||g_factPKNameKey||',CREATION_DATE,LAST_UPDATE_DATE) select ';
write_to_log_file_n('Inserted '||sql%rowcount||' rows into '||g_da_table||get_time);
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
l_stmt:=l_stmt||' as select '||g_fstgPKName||' '||g_factPKName||' from '||p_table||' MINUS select ';
l_stmt:='insert ';
'select /*+ORDERED*/ '||p_table||'.'||g_fstgPKName||','||p_table||'.'||g_factPKNameKey||','||
p_table||'.'||g_factPKName||'1,sysdate from '||l_table_A||','||p_table||' where '||
l_table_A||'.'||g_factPKName||'='||p_table||'.'||g_fstgPKName;
write_to_log_file_n('Inserted '||sql%rowcount||' rows into '||g_pp_table||get_time);
write_to_log_file_n('Error in insert '||sqlerrm);
write_to_log_file_n('Re-try insert ');
l_stmt:=l_stmt||' as select row_id from '||g_dup_hold_table||' MINUS select row_id from '||g_dup_pp_row_id_table;
l_stmt:='insert into '||g_dup_pp_row_id_table||'(row_id) select row_id from '||l_dup_pp_row_id_table;
write_to_log_file_n('Inserted '||sql%rowcount||' rows into '||g_dup_pp_row_id_table||get_time);
l_stmt:=l_stmt||' as select row_id from '||g_dup_hold_table;
at the end of the load, we need to look at the duplicate-collect records and insert into the PP table. we could have
pushed from instance 1 and instance 2 and loaded. we need to get the mapping between instance 1 and 2 into PP table
*/
function load_dup_coll_into_pp return boolean is
l_stmt varchar2(20000);
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
l_stmt:=l_stmt||' as select distinct '||g_fstgPKName;
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
l_stmt:=l_stmt||' as select '||g_factPKName||' from '||g_dup_pp_table||' MINUS select ';
l_stmt:='insert ';
l_stmt:=l_stmt||'into '||g_pp_table||' '||l_table||'(PK,PK_KEY,LOADED_PK,CREATION_DATE) select /*+ORDERED*/ '||
g_dup_pp_table||'.'||g_factPKName||','||g_dup_pp_table||'.'||g_factPKNameKey||','||g_dup_pp_table||'.LOADED_PK,'||
'sysdate from '||l_dup_pp_table_C||','||g_dup_pp_table||' where '||l_dup_pp_table_C||'.'||g_factPKName||'='||
g_dup_pp_table||'.'||g_factPKName;
write_to_log_file_n('Inserted into '||g_pp_table||' '||sql%rowcount||' rows'||get_time);
l_data_table_BF:=g_da_op_table||'BF';--to update DA. row_id2 from BB where not master instance
l_data_table_BG:=g_da_op_table||'BG';--IOT.row_id from F. if dup coll=no,where count=1. else distinct row_id.update DA
if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_fstgTableName,'LAST_UPDATE_DATE IS NOT NULL')=2 then
l_col:='LAST_UPDATE_DATE';
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
if l_col='LAST_UPDATE_DATE' then
l_stmt:=l_stmt||g_fstgTableName||'.'||l_col||' col,';
if l_col='LAST_UPDATE_DATE' then
l_stmt:='select 1 from '||l_data_table||' having count(*)>1 group by col';
write_to_log_file_n('LAST_UPDATE_DATE is duplicate. Trying with ROWNUM');
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
l_stmt:=l_stmt||' as select row_id,row_id1 from '||l_data_table_BO||' where '||
g_instance_column||'1<>'''||g_master_instance||'''';
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' as select row_id2 from '||l_data_table_BB||' where '||
g_instance_column||'<>'''||g_master_instance||'''';
l_stmt:=l_stmt||' as select distinct row_id2 row_id from '||l_data_table_BF;
l_stmt:=l_stmt||' as select row_id2 row_id from '||l_data_table_BF||' having count(row_id2)=1 '||
'group by row_id2';
l_stmt:='update /*+ORDERED USE_NL('||l_table||')*/ ';
'LAST_UPDATE_DATE=sysdate where rowid in (select row_id from '||l_data_table_BG||')';
write_to_log_file_n('Updated '||sql%rowcount||' rows '||get_time);
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' from '||l_data_table_BC||' MINUS select ';
l_stmt:=l_stmt||' as select /*+ORDERED*/ '||l_data_table_BC||'.row_id,'||l_data_table_BC||'.row_id1 from '||
l_data_table_BD||','||l_data_table_BC||' where ';
l_stmt:=l_stmt||' as select row_id,row_id1 from '||l_data_table_BA||' MINUS select row_id,row_id1 from '||
l_data_table_BE;
l_stmt:=l_stmt||' as select rowid row_id from '||l_data_table||' MINUS select row_id from '||l_data_table_B;
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' as select row_id from '||l_data_table||' MINUS select row_id from '||l_data_table_D;
l_stmt:=l_stmt||' as select * from '||l_data_table_D||' where '||
g_instance_column||'='''||g_master_instance||'''';
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' from '||l_data_table_D||' MINUS select ';
l_stmt:=l_stmt||' as select /*+ORDERED*/ '||l_data_table_D||'.* from '||l_data_table_DR||','||
l_data_table_D||' where ';
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' as select /*+ORDERED*/ '||l_dup_table||'.row_id from '||l_dup_max_table||','||
l_dup_table||' where ';
l_stmt:=l_stmt||' as select row_id from '||l_dup_table||' MINUS select row_id from '||l_dup_max_rowid_table;
l_stmt:=l_stmt||' as select max(col) col';
l_stmt:=l_stmt||' as select /*+ORDERED*/ '||l_data_table_DM||'.row_id from '||l_data_table_F||','||
l_data_table_DM||' where '||l_data_table_F||'.col='||l_data_table_DM||'.col and ';
l_stmt:=l_stmt||' as select max(col) col';
l_stmt:=l_stmt||' as select /*+ORDERED*/ '||l_data_table_DN||'.row_id from '||l_data_table_H||','||
l_data_table_DN||' where '||l_data_table_H||'.col='||l_data_table_DN||'.col and ';
l_stmt:=l_stmt||' as select row_id from '||l_data_table_G||' UNION ALL select row_id from '||l_data_table_I;
l_stmt:=l_stmt||' as select row_id from '||l_data_table||' MINUS select row_id from '||l_data_table_J;
l_stmt:=l_stmt||' as select /*+ORDERED*/ '||l_data_table_D||'.row_id from '||l_dup_value_table||','||
l_data_table_D||' where ';
l_stmt:=l_stmt||' UNION ALL select row_id1 row_id from '||l_data_table_B;
l_stmt:=l_stmt||' as select rowid row_id1, row_id from '||g_fact_dlog||' where row_id in '||
'(select rowid from '||g_object_name||')';
l_stmt:=l_stmt||' as select /*+ordered ';
l_stmt:='insert into '||g_fact_dlog||'(';
l_stmt:='insert /*+PARALLEL (A,'||g_parallel||')*/ into '||g_fact_dlog||' A (';
l_stmt:=l_stmt||') select ';
write_to_log_file('Inserted '||sql%rowcount||' rows '||get_time);
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' MINUS select row_id from '||p_surr_table;
l_stmt:=l_stmt||' as select '||l_auto_dang_table1||'.row_id,'||p_fk_name;
l_stmt:='select distinct '||g_instance_column||' from '||p_dang_table;
l_stmt:=l_stmt||' as select distinct '||p_fk_name||' from '||p_dang_table;
l_stmt:=l_stmt||' as select distinct '||p_fk_name||' from '||p_dang_table||
' where '||g_instance_column||' is null';
l_stmt:=l_stmt||' as select distinct '||p_fk_name||' from '||p_dang_table||
' where '||g_instance_column||'='''||p_dang_instance(z)||'''';
l_stmt:=l_stmt||' as select '||p_fk_name;
select
substr(x,1,(decode((instr(x,'-',1,1)),0,length(x)+1,instr(x,'-',1,1))-1)),
substr(x,decode(instr(x,'-',1,(2-1)),0,length(x)+1,instr(x,'-',1,(2-1)))+1,
(decode((instr(x,'-',1,2)),0,length(x)+1,(instr(x,'-',1,2)))-(instr(x,'-',1,(2-1))+1)))
from abc
data in abc
1011-1012-INST
100011-1-INST
1-100-INST
*/
for j in 1..l_number_pk_cols loop
if l_pk_cols(j)<>'INST' then
if j=1 then
l_stmt:=l_stmt||',substr('||p_fk_name||',1,(decode((instr('||p_fk_name||',''-'',1,1)),0,'||
'length('||p_fk_name||'),instr('||p_fk_name||',''-'',1,1))-1)) '||l_pk_cols(j);
function insert_into_parent_fk_log(
p_dang_instance EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_dang_instance number,
p_fk_name varchar2,
p_parent_table_id number,
p_parent_table_name varchar2,
p_dim_auto_dang_table varchar2,
p_dim_lowest_ltc_id number) return boolean is
l_auto_dang_table3 varchar2(200);
write_to_log_file_n('In insert_into_parent_fk_log');
l_stmt:='insert into '||l_auto_dang_table3||'(level_table,value';
l_stmt:=l_stmt||') select '||p_dim_lowest_ltc_id||','||p_fk_name;
write_to_log_file_n('Inserted '||sql%rowcount||' records '||get_time);
write_to_log_file_n('Error in insert_into_parent_fk_log '||g_status_message);
if insert_into_parent_fk_log(l_dang_instance,l_number_dang_instance,p_pk_name,p_parent_table_id,
p_parent_table_name,p_dim_auto_dang_table,p_dim_lowest_ltc_id)=false then
return false;
l_stmt:=l_stmt||' as select /*+ORDERED USE_NL('||g_fstgTableName||')*/ ';
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
write_to_log_file_n('Cannot insert into level table '||p_ltc_table||'. Its missing a unique '||
'index on '||l_pk);
l_stmt:=l_stmt||' as select '||p_pk||','||p_pk_key||' from '||p_ltc_table||
' where 1=2';
l_stmt:=l_stmt||' as select distinct '||p_dang_pk||' '||p_pk||' from '||p_dang_table;
l_stmt:=l_stmt||' as select '||p_pk||' from '||l_table_distinct||' MINUS select '||
p_pk||' from '||l_table_old;
l_stmt:=l_stmt||' as select '||p_pk||','||p_seq||'.NEXTVAL '||p_pk_key||' from '||l_table1;
l_stmt:='insert ';
'select '||p_pk||','||p_pk_key||','||p_pk||',sysdate from '||l_table_new;
l_stmt:='insert ';
l_stmt:=l_stmt||',creation_date,last_update_date) '||
'select '||p_pk||','||p_pk_key||','||p_pk;
write_to_log_file_n('Inserted '||sql%rowcount||' rows'||get_time);
l_stmt:='insert into '||l_table_old||'('||p_pk||','||p_pk_key||') ';
l_stmt:=l_stmt||' select B.pk,B.pk_key from '||l_table_new||' A,'||p_pp_table||' B where '||
'A.'||p_pk||'=B.pk';
l_stmt:=l_stmt||' select B.'||p_pk||',B.'||p_pk_key||' from '||l_table_new||' A,'||p_ltc_table||' B '||
'where A.'||p_pk||'=B.'||p_pk;
write_to_log_file_n('Inserted '||sql%rowcount||' rows'||get_time);
else --insert into ltc and/or dim table
l_stmt:='create table '||l_table_union||' tablespace '||g_op_table_space;
l_stmt:=l_stmt||' as select '||p_pk||','||p_pk_key||' from '||l_table_old||' UNION ALL '||
' select '||p_pk||','||p_pk_key||' from '||l_table_new;
l_stmt:=l_stmt||' as select B.row_id,A.'||p_pk_key||' pk_key '||
' from '||l_table_union||' A,'||p_dang_table||' B where A.'||p_pk||'=B.'||p_dang_pk;
l_stmt:=l_stmt||' as select '||p_pk||','||p_pk_key||' from '||l_table_union||
' MINUS select '||p_pk||','||p_pk_key||' from '||l_table_old;
l_stmt:='insert ';
l_stmt:=l_stmt||',creation_date,last_update_date) select '||p_pk||','||p_pk_key||','||p_pk;
write_to_log_file_n('Inserted '||sql%rowcount||' rows'||get_time);
write_to_log_file_n('Re-try a insert into level table after recreating the data');
l_stmt:='insert into '||l_table_old||'('||p_pk||','||p_pk_key||') ';
l_stmt:=l_stmt||' select A.'||p_pk||',A.'||p_pk_key||' from '||l_table_new||' A,'||p_ltc_table||' B '||
'where A.'||p_pk||'=B.'||p_pk;
write_to_log_file_n('Inserted '||sql%rowcount||' rows'||get_time);
l_stmt:=l_stmt||' as select '||p_pk||','||p_pk_key||' from '||l_table_union||
' MINUS select '||p_pk||','||p_pk_key||' from '||l_table_old;
l_stmt:='insert ';
l_stmt:=l_stmt||',creation_date,last_update_date) select '||p_pk||','||p_pk_key;
write_to_log_file_n('Inserted '||sql%rowcount||' rows'||get_time);
write_to_log_file_n('Re-try a insert into dim table after recreating the data');
l_stmt:='insert into '||l_table_old||'('||p_pk||','||p_pk_key||') ';
l_stmt:=l_stmt||' select A.'||p_pk||',A.'||p_pk_key||' from '||l_table_new||' A,'||p_dim_table||' B '||
'where A.'||p_pk||'=B.'||p_dim_pk;
write_to_log_file_n('Inserted '||sql%rowcount||' rows'||get_time);
function refind_insert_rows return boolean is
l_table1 varchar2(200);
write_to_log_file_n('In refind_insert_rows '||get_time);
l_stmt:=l_stmt||' as select rowid row_id1,';
l_stmt:=l_stmt||' as select /*+ORDERED USE_NL(B)*/ ';
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
l_stmt:=l_stmt||' as select /*+ordered use_nl(B)*/ ';
l_stmt:=l_stmt||' as select /*+ordered*/ ';
l_stmt:='update /*+ORDERED USE_NL(A)*/ '||g_surr_table||' A set (row_id1,operation_code)='||
'(select row_id1,1 from '||l_table3||' where '||l_table3||'.row_id=A.rowid) where A.rowid in '||
'(select row_id from '||l_table3||')';
write_to_log_file_n('Updated '||l_count||' rows'||get_time);
write_to_log_file_n('Could not update any rows for insert/update. Some other error');
write_to_log_file_n('Error in refind_insert_rows '||g_status_message);
function execute_insert_stmt(p_count number) return boolean is
l_insert_type varchar2(200);
write_to_log_file_n('In execute_insert_stmt '||get_time);
l_insert_type:='MASS';
l_table1:=g_insert_ctas_table||'R';
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Inserting into '||g_primary_target_name,sysdate,
null,'MAPPING','INSERT',g_jobid_stmt||'INSERT'||p_count,'I');
<>
if g_fact_audit or g_fact_net_change then
if EDW_OWB_COLLECTION_UTIL.drop_table(g_fact_audit_net_table)= false then
null;
when there is audit or net change g_insert_stmt creates g_fact_audit_net_table
*/
if g_debug then
write_to_debug_n('Going to INSERT rows '||get_time);
if l_insert_type='ROW-BY-ROW' and g_fact_audit=false and g_fact_net_change=false then
if g_debug then
write_to_log_file_n('ROW-BY-ROW inserts '||get_time);
if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_ctas_table)=false then
null;
write_to_log_file_n(g_insert_stmt_ctas||get_time);
execute immediate g_insert_stmt_ctas;
l_stmt:=l_stmt||' as select rowid row_id from '||g_insert_ctas_table;
l_stmt:='select row_id from '||l_table1;
execute immediate g_insert_stmt_row using l_rowid;
write_to_log_file_n('Error in insert '||sqlerrm||get_time);
write_to_log_file_n('Error in g_insert_stmt '||g_status_message);
if refind_insert_rows=false then
return false;
if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_ctas_table)=false then
null;
g_total_insert:=g_number_rows_processed;
write_to_log_file_n('MASS inserts '||get_time);
execute immediate g_insert_stmt;
g_total_insert:=g_number_rows_processed;
write_to_log_file_n('Inserted '||sql%rowcount||' rows'||get_time);
write_to_log_file_n('Insert failed '||sqlerrm||get_time);
write_to_log_file_n('Unique constraint violated. Attempting again after finding rows for insert');
if refind_insert_rows=false then
return false;
goto start_insert;
l_insert_type:='ROW-BY-ROW';
goto start_insert;
goto start_insert;
write_to_log_file_n('Error in g_insert_stmt '||g_status_message);
write_to_log_file_n('Error in g_insert_stmt '||g_status_message);
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'INSERT'||p_count,'U');
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Process Audit or Netchange Records',sysdate,
null,'MAPPING','INSERT',g_jobid_stmt||'INSERTAN'||p_count,'I');
if l_insert_type='ROW-BY-ROW' then
g_number_rows_processed:=0;
l_stmt:=l_stmt||' as select rowid row_id from '||g_fact_audit_net_table;
l_stmt:='select row_id from '||l_table1;
execute immediate g_audit_net_insert_stmt_row using l_rowid;
write_to_log_file_n('Error in g_audit_net_insert_stmt_row '||g_status_message);
g_total_insert:=g_number_rows_processed;
if refind_insert_rows=false then
return false;
execute immediate g_insert_stmt;
write_to_debug_n('MASS INSERT. Going to execute g_audit_net_insert_stmt'||get_time);
execute immediate g_audit_net_insert_stmt;
g_total_insert:=g_number_rows_processed;
write_to_log_file_n('Inserted '||g_number_rows_processed||' rows '||get_time);
write_to_log_file_n('Unique constraint violated. Attempting again after finding rows for insert');
if refind_insert_rows=false then
return false;
goto start_insert;
goto start_insert;
write_to_log_file_n('Error in g_audit_net_insert_stmt '||g_status_message);
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'INSERTAN'||p_count,'U');
write_to_log_file_n('Inserted '||g_number_rows_processed||' Rows '||get_time);
insert_into_load_progress_d(g_load_pk,g_primary_target_name,g_jobid_stmt||'Process Audit or Netchange Records',sysdate,
null,'MAPPING','INSERT',g_jobid_stmt||'INSERTANN'||p_count,'I');
if execute_fa_nc_insert('INSERT') = false then
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'INSERTANN'||p_count,'U');
insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,g_jobid_stmt||'INSERTANN'||p_count,'U');
write_to_log_file_n('Error in execute_insert_stmt '||g_status_message);
if g_ok_switch_update=100 then
g_type_ok_generation:='UPDATE';
write_to_debug_n('g_type_ok_generation made UPDATE');
if l_percentage
write_to_debug_n('g_type_ok_generation made UPDATE');
g_stmt:=g_stmt||' as select row_id from '||g_ok_rowid_table||' where status=1';
g_stmt:=g_stmt||' as select /*+ORDERED USE_NL('||g_fstgTableName||')*/ ';
g_stmt:=g_stmt||' as select /*+ORDERED*/ ';
if g_last_update_date_flag then
g_stmt:=g_stmt||',last_update_date';
l_smart_update varchar2(10);
l_smart_update_table varchar2(80);
l_smart_update_table:=p_table_name||'_SU';
g_stmt:='select '||
'object_name'||
',mapping_id'||
',map_type'||
',primary_src'||
',primary_target'||
',primary_target_name'||
',object_type'||
',conc_id'||
',conc_program_name'||
',fact_audit'||
',net_change'||
',fact_audit_name'||
',net_change_name'||
',fact_audit_is_name'||
',net_change_is_name'||
',debug'||
',duplicate_collect'||
',execute_flag'||
',request_id'||
',collection_size'||
',parallel'||
',table_owner'||
',bis_owner'||
',temp_log'||
',forall_size'||
',update_type'||
',p_mode'||
',explain_plan_check'||
',fact_dlog'||
',key_set'||
',instance_type'||
',load_pk'||
',fresh_restart'||
',op_table_space'||
',da_table'||
',pp_table'||
',master_instance'||
',rollback'||
',smart_update'||
',fk_use_nl'||
',fact_smart_update'||
',auto_dang_table_extn'||
',log_dang_keys'||
',create_parent_table_records'||
',check_fk_change'||
',stg_join_nl_percentage'||
',ok_switch_update'||
',stg_make_copy_percentage'||
',ok_table'||
',hash_area_size'||
',sort_area_size'||
',trace_mode'||
',read_cfig_options'||
',job_status_table'||
',max_round'||
',update_dlog_lookup_table'||
',dlog_has_data'||
',total_records'||
',stg_copy_table_flag,'||
'sleep_time,'||
'parallel_drill_down'||
' from '||p_table_name;
,g_update_type
,g_mode
,l_explain_plan_check
,g_fact_dlog
,g_key_set
,g_instance_type
,g_load_pk
,l_fresh_restart
,g_op_table_space
,g_da_table
,g_pp_table
,g_master_instance
,g_rollback
,l_smart_update
,g_fk_use_nl
,g_fact_smart_update
,g_auto_dang_table_extn
,l_log_dang_keys
,l_create_parent_table_records
,l_check_fk_change
,g_stg_join_nl_percentage
,g_ok_switch_update
,g_stg_make_copy_percentage
,g_main_ok_table_name
,g_hash_area_size
,g_sort_area_size
,l_trace
,l_read_cfig_options
,g_job_status_table
,g_max_round
,g_update_dlog_lookup_table
,l_dlog_has_data
,g_total_records
,l_stg_copy_table_flag
,g_sleep_time
,l_parallel_drill_down;
g_smart_update:=false;
if l_smart_update='Y' then
g_smart_update:=true;
write_to_log_file('g_update_type='||g_update_type);
write_to_log_file('l_smart_update='||l_smart_update);
write_to_log_file('g_fact_smart_update='||g_fact_smart_update);
write_to_log_file('g_ok_switch_update='||g_ok_switch_update);
write_to_log_file('g_update_dlog_lookup_table='||g_update_dlog_lookup_table);
g_stmt:='select col_name from '||l_skip_cols_table;
g_stmt:='select col_name,stg_col_name from '||l_da_cols_table;
g_stmt:='select col_name from '||l_skip_levels_table;
g_stmt:='select col_name from '||l_smart_update_table;
g_number_smart_update_cols:=1;
fetch cv into g_smart_update_cols(g_number_smart_update_cols);
g_number_smart_update_cols:=g_number_smart_update_cols+1;
g_number_smart_update_cols:=g_number_smart_update_cols-1;
write_to_log_file_n('Smart Update Columns');
for i in 1..g_number_smart_update_cols loop
write_to_log_file(g_smart_update_cols(i));
write_to_log_file_n(l_ok_rowid_table||' has status 1. we need to update interface table '||
'to READY for these rows');
if update_stg_status_column(l_ok_rowid_table,'row_id',' where status=1 ','READY',true)=false then
return false;
g_stmt:=g_stmt||' as select '||l_ok_table||'.*,rownum row_num from '||l_ok_table;
g_skip_ilog_update:=true;
l_parameter(13):='p_update_type';
function insert_fm_ff_table
return boolean is
------
l_fstg_measures edw_owb_collection_util.varcharTableType;
write_to_log_file_n('In insert_fm_ff_table '||get_time);
g_stmt:='insert all into '||g_user_measure_table||'(';
g_stmt:=substr(g_stmt,1,length(g_stmt)-1)||') select ';
l_stmt:=l_stmt||' as select row_id from '||g_ok_rowid_table||' where status=1';
write_to_log_file_n('Error in insert_fm_ff_table '||g_status_message);
if edw_owb_collection_util.update_status_table(g_dd_status_table,'status','RUNNING','where parent_ltc_id='||
g_primary_target)=false then
return false;
if edw_owb_collection_util.update_status_table(g_dd_status_table,'job_id',g_ltc_drill_down_job_id,
'where parent_ltc_id='||g_primary_target)=false then
return false;
l_stmt:='select child_ltc,child_ltc_id from '||p_dd_table||' where parent_ltc_id=:1 and '||
'child_ltc_id is not null order by level_order';
l_stmt:='select parent_ltc,parent_ltc_id from '||p_dd_table||' where child_ltc_id=:1 order by level_order';
if merge_all_update_rowids(p_parent_id,p_ul_table,p_ur_pattern,l_parent_pci_table,l_num_parent_ltc)=false then
raise l_exception;
if edw_owb_collection_util.update_status_table(p_dd_table,'status','DONE','where parent_ltc_id='||
p_parent_id||' and child_ltc_id='||l_child_ltc_id(i))=false then
null;
if edw_owb_collection_util.update_status_table(p_dd_table,'status','DONE','where parent_ltc_id='||
p_parent_id)=false then
null;
if edw_owb_collection_util.update_status_table(p_dd_table,'status','ERROR+++'||g_status_message,
'where parent_ltc_id='||p_parent_id)=false then
null;
if edw_owb_collection_util.update_status_table(p_dd_table,'status','ERROR+++'||sqlerrm,'where parent_ltc_id='||
p_parent_id)=false then
null;
select distinct
pk_col.column_name,
fk_col.column_name
from
edw_level_relations_md_v lvl_rel,
edw_pvt_key_columns_md_v pk_key,
edw_pvt_columns_md_v pk_col,
edw_pvt_key_columns_md_v fk_key,
edw_pvt_columns_md_v fk_col
where
lvl_rel.PARENT_LVLTBL_ID=p_parent_id
and lvl_rel.CHILD_LVLTBL_ID=p_child_id
and pk_key.key_id=lvl_rel.uk_id
and pk_col.column_id=pk_key.column_id
and fk_key.key_id=lvl_rel.fk_id
and fk_col.column_id=fk_key.column_id;
'select distinct '||
'pk_col.column_name, '||
'fk_col.column_name '||
'from '||
'edw_level_relations_md_v lvl_rel, '||
'edw_pvt_key_columns_md_v pk_key, '||
'edw_pvt_columns_md_v pk_col, '||
'edw_pvt_key_columns_md_v fk_key, '||
'edw_pvt_columns_md_v fk_col '||
'where '||
'lvl_rel.PARENT_LVLTBL_ID=p_parent_id '||
'and lvl_rel.CHILD_LVLTBL_ID=p_child_id '||
'and pk_key.key_id=lvl_rel.uk_id '||
'and pk_col.column_id=pk_key.column_id '||
'and fk_key.key_id=lvl_rel.fk_id '||
'and fk_col.column_id=fk_key.column_id; ');
l_stmt:=l_stmt||' as select ';
for a ltc, find out all update rowid tables and merge into UL table.
UL=update L
if UL table does not exist, create one.
we dont care for checking to see if ul table exists because in case of error
recovery, we go the old route of snplog
*/
function merge_all_update_rowids(
p_ltc_id number,
p_ul_table varchar2,
p_ur_pattern varchar2,
p_pci_tables EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_num_pci_tables number
)return boolean is
--
l_table EDW_OWB_COLLECTION_UTIL.varcharTableType;
write_to_log_file_n('In merge_all_update_rowids '||get_time);
l_stmt:=l_stmt||' select row_id from '||l_table(i)||' union all';
l_stmt:=l_stmt||' select row_id from '||l_pci_table(i)||' union';
write_to_log_file_n('Error in merge_all_update_rowids '||g_status_message);
select LOG_TABLE from all_snapshot_logs where master=p_ltc and LOG_OWNER=p_table_owner;
select dim_id,LEVEL_PREFIX from edw_levels_md_v where LEVEL_TABLE_NAME=p_ltc;
write_to_log_file_n('select dim_id,LEVEL_PREFIX from edw_levels_md_v where LEVEL_TABLE_NAME='||p_ltc);
write_to_log_file_n('select LOG_TABLE from all_snapshot_logs where master='||p_ltc||
' and LOG_OWNER='||p_table_owner);
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' union select row_id row_id';