The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_update_type varchar2,
p_fact_dlog varchar2,
p_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_skip_cols number,
p_load_fk number,
p_fresh_restart boolean,
p_op_table_space varchar2,
p_bu_tables EDW_OWB_COLLECTION_UTIL.varcharTableType,--before update tables.prop dim change to derv
p_bu_dimensions EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_bu_tables number,
p_bu_src_fact varchar2,--what table to look at as the src fact. if null, scan the actual src fact
p_load_mode varchar2,
p_rollback varchar2,
p_src_join_nl_percentage number,
p_thread_type varchar2,
p_max_threads number,
p_min_job_load_size number,
p_sleep_time number,
p_hash_area_size number,
p_sort_area_size number,
p_trace boolean,
p_read_cfig_options boolean
) return boolean is
l_pre_hook varchar2(10);
p_update_type,
p_fact_dlog,
p_skip_cols,
p_number_skip_cols,
p_load_fk,
g_fresh_restart,
p_op_table_space,
p_bu_tables,
p_bu_dimensions,
p_number_bu_tables,
p_bu_src_fact,
p_load_mode,
p_rollback,
p_src_join_nl_percentage,
l_pre_hook,
l_post_hook
) = false then
write_to_log_file_n('EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT returned with error '||get_time);
p_update_type varchar2,
p_fact_dlog varchar2,
p_fresh_restart boolean,
p_op_table_space varchar2,
p_bu_tables EDW_OWB_COLLECTION_UTIL.varcharTableType,--before update tables.prop dim change to derv
p_bu_dimensions EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_bu_tables number,
p_bu_src_fact varchar2,--what table to look at as the src fact. if null, scan the actual src fact
p_load_mode varchar2,
p_rollback varchar2,
p_src_join_nl_percentage number,
p_thread_type varchar2,
p_max_threads number,
p_min_job_load_size number,
p_sleep_time number,
p_hash_area_size number,
p_sort_area_size number,
p_trace boolean,
p_read_cfig_options boolean,
p_job_queue_processes number
)return boolean is
ll_derived_facts EDW_OWB_COLLECTION_UTIL.varcharTableType;
write_to_log_file_n('The derived facts for inc update');
p_update_type,
p_fact_dlog,
l_skip_cols,
l_number_skip_cols,
p_load_pk(i),
g_fresh_restart,
p_op_table_space,
p_bu_tables,
p_bu_dimensions,
p_number_bu_tables,
p_bu_src_fact,
p_load_mode,
p_rollback,
p_src_join_nl_percentage,
p_max_threads,
p_min_job_load_size,
p_sleep_time,
l_job_status_table(i),
p_hash_area_size,
p_sort_area_size,
p_trace,
p_read_cfig_options
)=false then
return false;
if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(l_input_table(i),-1)=false then
return false;
if EDW_OWB_COLLECTION_UTIL.update_inp_table_concid(l_input_table(i),l_job_id(i))=false then
return false;
update the inp table for the job id
*/
if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(l_input_table(i),l_job_id(i))=false then
return false;
if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(l_input_table(i),l_job_id(i))=false then
return false;
must have delete_object_log_tables and inp tables etc
must terminate jobs if there is error
*/
end loop;
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,
p_fact_dlog,
l_skip_cols,
l_number_skip_cols,
p_load_pk(i),
g_fresh_restart,
p_op_table_space,
p_bu_tables,
p_bu_dimensions,
p_number_bu_tables,
p_bu_src_fact,
p_load_mode,
p_rollback,
p_src_join_nl_percentage,
l_pre_hook,
l_post_hook
);
if delete_object_log_tables(
p_src_fact_name,
p_table_owner,
p_bis_owner,
p_fact_dlog,
l_ilog,
l_dlog,
l_number_derived_facts)=false then
return false;
p_update_type varchar2,
p_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_skip_cols number,
p_load_fk number,
p_fresh_restart boolean,
p_op_table_space varchar2,
p_rollback varchar2,
p_src_join_nl_percentage number,
p_thread_type varchar2,
p_max_threads number,
p_min_job_load_size number,
p_sleep_time number,
p_hash_area_size number,
p_sort_area_size number,
p_trace boolean,
p_read_cfig_options boolean
) return boolean is
l_ins_rows_processed number;
p_update_type,
null,
p_skip_cols,
p_number_skip_cols,
p_load_fk,
g_fresh_restart,
p_op_table_space,
l_bu_tables,
l_bu_dimensions,
l_number_bu_tables,
l_bu_src_fact,
l_load_mode,
p_rollback,
p_src_join_nl_percentage,
p_max_threads,
p_min_job_load_size,
p_sleep_time,
l_job_status_table(i),
p_hash_area_size,
p_sort_area_size,
p_trace,
p_read_cfig_options
)=false then
return false;
if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(l_input_table(i),l_job_id(l_number_jobs))=false then
return false;
if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(l_input_table(i),l_job_id(l_number_jobs))=false then
return false;
if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(l_input_table(i),l_job_id(l_number_jobs))=false then
return false;
p_update_type,
null,
p_skip_cols,
p_number_skip_cols,
p_load_fk,
g_fresh_restart,
p_op_table_space,
l_bu_tables,
l_bu_dimensions,
l_number_bu_tables,
l_bu_src_fact,
l_load_mode,
p_rollback,
p_src_join_nl_percentage,
l_pre_hook,
l_post_hook) = false then
write_to_log_file_n('EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT returned with error '||get_time);
l_stmt:='select fact_id from edw_facts_md_v where fact_name=:s';
l_stmt:='select fk_item.column_name, fk.foreign_key_name, dim.dim_name, '||
'dim.dim_id,pk_item.column_name '||
'from '||
'edw_foreign_keys_md_v fk, '||
'edw_pvt_key_columns_md_v isu, '||
'edw_pvt_columns_md_v fk_item, '||
'edw_unique_keys_md_v pk, '||
'edw_pvt_key_columns_md_v pisu, '||
'edw_pvt_columns_md_v pk_item, '||
'edw_dimensions_md_v dim '||
'where '||
'fk.entity_id=:a '||
'and isu.key_id=fk.foreign_key_id '||
'and fk_item.column_id=isu.column_id '||
'and fk.key_id=pk.key_id '||
'and pisu.key_id=pk.key_id '||
'and pk_item.column_id=pisu.column_id '||
'and dim.dim_id=pk.entity_id ';
l_stmt:='select lvl.level_name,lvl.level_prefix, dim.dim_name from edw_dimensions_md_v dim, '||
'edw_levels_md_v lvl where dim.dim_name in ('||l_in_stmt||') and lvl.dim_id=dim.dim_id';
l_stmt:='select pk_item.column_name, substr(pk_item.column_name,1,instr(pk_item.column_name,''_'',1)-1) '||
'from edw_unique_keys_md_v pk,edw_pvt_key_columns_md_v isu, edw_pvt_columns_md_v pk_item '||
'where pk.entity_id=:a and isu.key_id=pk.key_id and pk_item.column_id=isu.column_id';
l_stmt:='select map.mapping_id, src.relation_name, src.relation_id '||
'from '||
'edw_pvt_map_properties_md_v map, '||
'edw_relations_md_v src '||
'where map.primary_target=:s '||
'and src.relation_id=map.primary_source';
p_ins_rows_insert number,
p_ins_rows_update number,
p_ins_rows_delete number,
p_message varchar2,
p_status varchar2,
p_load_pk number
) return boolean is
Begin
if EDW_OWB_COLLECTION_UTIL.write_to_collection_log(
p_object_name,
p_object_id,
p_object_type,
p_conc_program_id,
p_collection_start_date,
p_collection_end_date,
p_ins_rows_ready,
p_ins_rows_processed,
p_ins_rows_collected,
p_ins_rows_insert,
p_ins_rows_update,
p_ins_rows_delete,
p_message,
p_status,
p_load_pk)= false then
return false;
l_ins_rows_insert number;
l_ins_rows_update number;
l_ins_rows_delete number;
l_ins_rows_insert,
l_ins_rows_update,
l_ins_rows_delete,
l_ins_instance_name,
l_ins_request_id_table)=false then
null;
function delete_object_log_tables(
p_src_fact varchar2,
p_table_owner varchar2,
p_bis_owner varchar2,
p_fact_dlog varchar2,
p_ilog EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_dlog EDW_OWB_COLLECTION_UTIL.varcharTableType,
p_number_derv_fact number
)return boolean is
l_snp_log varchar2(400);
write_to_log_file_n('In delete_object_log_tables');
write_to_log_file_n('Error in delete_object_log_tables '||g_status_message);