The following lines contain the word 'select', 'insert', 'update' or 'delete':
if delete_dim_duplicates=false then
return false;
if delete_fact_duplicates=false then
return false;
l_stmt:='select pk_item.column_name, substr(pk_item.column_name,1,instr(upper(pk_item.column_name),''_KEY'')-1) '||
'from edw_dimensions_md_v rel , '||
'edw_unique_keys_md_v pk, '||
'edw_pvt_key_columns_md_v isu, '||
'edw_pvt_columns_md_v pk_item '||
'where '||
'rel.dim_name=:a '||
'and pk.entity_id=rel.dim_id '||
'and pk.primarykey=1 '||
'and isu.key_id=pk.key_id '||
'and pk_item.column_id=isu.column_id';
l_stmt:=' select ltc.name '||
'from '||
'edw_tables_md_v ltc, '||
'edw_dimensions_md_v dim, '||
'edw_levels_md_v lvl '||
'where dim.dim_name=:a '||
'and lvl.dim_id=dim.dim_id '||
'and ltc.name=lvl.level_name||''_LTC''';
l_stmt:='select pk_item.column_name, pk_item.data_type '||
'from edw_relations_md_v rel , '||
'edw_unique_keys_md_v pk, '||
'edw_pvt_key_columns_md_v isu, '||
'edw_pvt_columns_md_v pk_item '||
'where '||
'rel.relation_name=:a '||
'and pk.entity_id=rel.relation_id '||
'and isu.key_id=pk.key_id '||
'and pk_item.column_id=isu.column_id '||
'and pk_item.column_name like ''%_PK''';
l_stmt:='select pk_item.column_name, pk_item.data_type '||
'from edw_relations_md_v rel , '||
'edw_unique_keys_md_v pk, '||
'edw_pvt_key_columns_md_v isu, '||
'edw_pvt_columns_md_v pk_item '||
'where '||
'rel.relation_name=:a '||
'and pk.entity_id=rel.relation_id '||
'and isu.key_id=pk.key_id '||
'and pk_item.column_id=isu.column_id ';
function delete_dim_duplicates return boolean is
Begin
write_to_log_file_n('delete_dim_duplicates');
if delete_dim_duplicate_data(g_dim_name,g_dim_pk,g_dim_pk_key)=false then
return false;
if delete_table_duplicates(g_ltc_tables(i),g_ltc_pk(i),g_ltc_pk_key(i))=false then
return false;
g_status_message:='Error in delete_dim_duplicates function '||sqlerrm;
function delete_fact_duplicates return boolean is
Begin
write_to_log_file_n('delete_fact_duplicates');
if delete_table_duplicates(g_fact_name,g_fact_pk,g_fact_pk_key)=false then
return false;
g_status_message:='Error in delete_fact_duplicates function '||sqlerrm;
function delete_table_duplicates(p_table varchar2,p_pk varchar2,p_pk_key varchar2) return boolean is
l_stmt varchar2(8000);
write_to_log_file_n('delete_table_duplicates');
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
l_stmt:=l_stmt||' as select max('||p_pk_key||') '||p_pk_key||' from '||l_dup_table||
' group by '||p_pk;
l_stmt:=l_stmt||' as select /*+ORDERED*/ '||l_dup_table||'.row_id from '||l_dup_max_table||','||
l_dup_table||' where '||l_dup_max_table||'.'||p_pk_key||'='||l_dup_table||'.'||p_pk_key;
l_stmt:=l_stmt||' as select row_id from '||l_dup_table||' MINUS select row_id from '||
l_dup_max_rowid_table;
l_stmt:='delete /*+ORDERED USE_NL('||p_table||')*/ '||p_table||' where rowid in (select row_id from '||
l_dup_rowid_table||')';
write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
g_status_message:='Error in delete_table_duplicates function '||sqlerrm;
function delete_dim_duplicate_data(
p_dim_name varchar2,
p_dim_pk varchar2,
p_dim_pk_key varchar2
)return boolean is
-------
l_pk_table varchar2(200);
l_dup_update_table varchar2(200);
write_to_log_file_n('delete_dim_duplicate_data');
l_dup_update_table:=g_bis_owner||'.'||l_name||'U';
if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_update_table)=false then
null;
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' as select ';
l_stmt:=l_stmt||' as select '||p_dim_pk||', max('||p_dim_pk_key||') '||p_dim_pk_key||' from '||l_dup_table||
' group by '||p_dim_pk;
l_stmt:='create table '||l_dup_update_table||' tablespace '||g_op_table_space;
l_stmt:=l_stmt||' as select dup.row_id,dup.'||p_dim_pk_key||',max_dup.'||p_dim_pk_key||' max_'||p_dim_pk_key||
' from '||l_dup_max_table||' max_dup,'||l_dup_table||' dup '||
'where dup.'||p_dim_pk||'=max_dup.'||p_dim_pk||' and dup.'||p_dim_pk_key||'<>'||
'max_dup.'||p_dim_pk_key;
l_stmt:='create unique index '||l_dup_update_table||'U1 on '||l_dup_update_table||'(row_id) '||
'tablespace '||g_op_table_space;
l_stmt:='create unique index '||l_dup_update_table||'U2 on '||l_dup_update_table||'('||p_dim_pk_key||') '||
'tablespace '||g_op_table_space;
EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dup_update_table,instr(l_dup_update_table,'.')+1,
length(l_dup_update_table)),substr(l_dup_update_table,1,instr(l_dup_update_table,'.')-1));
l_stmt:='update ';
l_stmt:=l_stmt||l_fact(i)||' set ('||l_fact_fk(i)||')=(select max_'||p_dim_pk_key||' from '||
l_dup_update_table||' where '||l_dup_update_table||'.'||p_dim_pk_key||'='||l_fact(i)||'.'||l_fact_fk(i)||
') ';
l_stmt:=l_stmt||' where '||l_fact(i)||'.'||l_fact_fk(i)||' in (select '||p_dim_pk_key||' from '||
l_dup_update_table||')';
write_to_log_file_n('Updated '||sql%rowcount||' rows '||get_time);
l_stmt:='delete /*+ORDERED USE_NL('||p_dim_name||')*/ '||p_dim_name||' where rowid in (select row_id from '||
l_dup_update_table||')';
write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_update_table)=false then
null;
g_status_message:='Error in delete_dim_duplicate_data function '||sqlerrm;
l_stmt:='select relation_name from edw_relations_md_v where relation_long_name=:a';
l_stmt:='select 1 from edw_dimensions_md_v where dim_name=:a';
l_stmt:='select '||
'fact.fact_name,fk_col.column_name '||
'from '||
'edw_facts_md_v fact, '||
'edw_foreign_keys_md_v fk, '||
'EDW_PVT_KEY_COLUMNS_MD_V fku, '||
'edw_pvt_columns_md_v fk_col, '||
'edw_unique_keys_md_v pk, '||
'edw_dimensions_md_v dim '||
'where '||
'fact.fact_id=fk.entity_id '||
'and fk.foreign_key_id=fku.key_id '||
'and fk_col.column_id=fku.column_id '||
'and fk_col.parent_object_id=fact.fact_id '||
'and pk.key_id=fk.key_id '||
'and pk.entity_id=dim.dim_id '||
'and dim.dim_name=:1 '||
'order by fact.fact_name';