[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** 'U' - Updated
*/
procedure STORE(X_TABLE_OWNER in varchar2, X_TABLE_NAME in varchar2) is
L_STATUS varchar2(1);
select status
into l_status
from ad_patched_tables
where owner = x_table_owner
and name = x_table_name;
update ad_patched_tables
set status='U'
where owner = x_table_owner
and name = x_table_name;
insert into ad_patched_tables(owner, name, status)
values (x_table_owner, x_table_name, 'N');
select ev.view_name
into l_ev_name
from dba_editioning_views ev
where ev.owner = x_table_owner
and ev.view_name = ad_zd_table.ev_view(x_table_name);
select ev.table_name
into l_table_name
from dba_editioning_views ev
where ev.owner = x_ev_owner
and ev.view_name = replace(x_ev_name, '$', '#');
select evc.table_column_name
into l_column_name
from dba_editioning_view_cols evc
where evc.owner = replace(x_ev_owner, '$', '#')
and evc.view_name = x_ev_name
and evc.view_column_name = x_column_name;
select 'Y'
into l_is_seed
from dba_tab_columns c
where c.owner = x_table_owner
and c.table_name = x_table_name
and c.column_name = 'ZD_EDITION_NAME';
select distinct
tpt.grantee
, tpt.privilege
, tpt.grantable
, tpt.hierarchy
from
dba_tab_privs tpt
where tpt.owner = p_table_owner
and tpt.table_name = p_table_name
and tpt.privilege in ('SELECT', 'UPDATE', 'INSERT', 'DELETE', 'DEBUG')
and tpt.grantee <> 'SYSTEM'
and not exists
( select 'x'
from dba_tab_privs tpv
where tpv.owner = tpt.owner
and tpv.table_name = p_view_name
and tpv.grantee = tpt.grantee
and tpv.privilege = tpt.privilege
)
order by grantee;
if (nvl(grant_rec.hierarchy, 'NO') = 'YES' and grant_rec.privilege='SELECT' ) then
l_ev_str_privilege := l_ev_str_privilege || ' WITH HIERARCHY OPTION ';
select owner, synonym_name
from dba_synonyms
where table_name= p_table_name
and table_owner = p_table_owner
union all
select s.owner , s.synonym_name
from dba_synonyms s,
p
where s.table_name = p.synonym_name
and s.table_owner = p.owner
and s.table_owner in
( select oracle_username
from system.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', /*'U',*/ 'C')
)
)
cycle owner, synonym_name set cyclemarker to 'Y' default 'N'
select distinct synonym_name from p where cyclemarker = 'N' ;
select owner, trigger_name, trigger_type, status
from dba_triggers
where table_owner in
( select oracle_username from system.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C') )
-- EXCLUDE: trigger generated by the Oracle Text Indexing
and trigger_name not like 'DR$%'
-- EXCLUDE: Editioned Data Storage Maintenance Trigger Name
and trigger_name <> ad_zd_seed.eds_trigger(x_table_name)
and trigger_name <> ad_zd_seed.eds_fcet(x_table_name)
-- EXCLUDE: cross edition triggers.
and crossedition = 'NO'
and table_owner = x_table_owner
and (
(table_name = x_table_name and base_object_type='TABLE')
or
(table_name=ev_view(x_table_name) and base_object_type='VIEW')
)
and owner in
(select oracle_username from system.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C') );
select syn.owner, syn.synonym_name
into l_synonym_owner, l_synonym_name
from dba_synonyms syn
where syn.owner = ad_zd.apps_schema
and syn.table_owner = x_table_owner
and syn.table_name = l_ev_name
and rownum < 2;
select status
into l_valid_status
from dba_objects
where owner = trg_rec.owner
and object_name = trg_rec.trigger_name
and object_type = 'TRIGGER';
select object_owner ,
object_name ,
policy_group ,
policy_name ,
pf_owner ,
package ,
function ,
sel ,
ins ,
upd ,
del ,
idx ,
-- chk_option ,
decode(chk_option, 'YES', 'true', 'false'),
--enable ,
decode(enable, 'YES', 'true', 'false'),
--static_policy ,
decode(static_policy ,'YES', 'true', 'false'),
policy_type ,
--long_predicate
decode(long_predicate, 'YES', 'true', 'false')
from dba_policies
where object_owner = x_owner
and object_name = x_name
and policy_name = UPPER(policy_name) -- EXCLUDE: internal polciy, if any.
and lower(policy_name) <> 'ad_zd_seed'; -- EXCLUDE: AD_ZD_SEED policies .
l_stmt_types := 'SELECT ';
l_stmt_types := 'INSERT';
l_stmt_types := l_stmt_types || ', INSERT';
l_stmt_types := 'UPDATE';
l_stmt_types := l_stmt_types || ', UPDATE';
l_stmt_types := 'DELETE';
l_stmt_types := l_stmt_types || ', DELETE';
' statement_types=>''' || l_stmt_types || ''', ' || /* statement_types =>'SELECT,INDEX, INSERT, UPDATE, DELETE.*/
' update_check=>' || l_chk_option || ', ' || /* BOOLEAN value, so extra single quote required otherwise that would become
string */
' enable=>' || l_enable || ', ' || /* BOOLEAN */
' static_policy=>' || l_static_policy || ', ' || /* BOOLEAN */
' policy_type=>' || l_policy_type || ', ' || /* integer VALUE */
' long_predicate=>' || l_long_predicate || ', ' || /* BOOLEAN */
' sec_relevant_cols=>NULL, ' ||
' sec_relevant_cols_opt=>NULL); END; ' ;
select distinct d.owner --, d1.name, d1.type
from dba_dependencies d
where d.owner in ( select oracle_username
from system.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C' )
)
and d.referenced_type = 'SYNONYM'
and d.referenced_owner = 'PUBLIC'
and d.referenced_name = p_synonym_name
and not exists ( select 1
from dba_synonyms
where owner = d.owner
and synonym_name= p_synonym_name
and table_owner = x_table_owner
and table_name in (x_table_name, x_ev_name)
);
select syn.synonym_name
from dba_synonyms syn,
DBA_TABLES tab
where syn.owner='PUBLIC'
and syn.table_owner in ( select oracle_username
from system.fnd_oracle_userid
where read_only_flag in ('E', 'A', 'B', 'C')
)
and syn.table_owner = x_table_owner
and syn.table_name = x_table_name
and tab.owner = syn.table_owner
and tab.table_name = syn.table_name;
select
ad_zd_table.ev_view_column(col.column_name) view_column_name
, max(col.column_name) table_column_name
, min(nvl(evc.view_column_id, 1000+col.column_id)) view_column_id
from dba_tab_columns col,
dba_editioning_view_cols evc
where col.owner = x_table_owner
and col.table_name = x_table_name
and evc.owner(+) = col.owner
and evc.view_name(+) = ad_zd_table.ev_view(col.table_name)
and evc.view_column_name(+) = ad_zd_table.ev_view_column(col.column_name)
group by ad_zd_table.ev_view_column(col.column_name)
order by view_column_id;
x_table_owner||'"."'||l_ev_name||'" as select ';
select
syn.owner owner
, syn.synonym_name synonym_name
, syn.table_name table_name
from dba_synonyms syn
where syn.table_owner = x_table_owner
and syn.table_name in (x_table_name, x_ev_name)
and syn.owner in
( select oracle_username
from system.fnd_oracle_userid
where read_only_flag in ('A','B', 'E', 'U', 'C') );
select tab.owner, tab.table_name
into l_table_owner, l_table_name
from dba_tables tab
where tab.owner = x_table_owner
and tab.table_name = x_table_name;
select
tab.owner table_owner
, tab.table_name table_name
from dba_tables tab
where tab.owner in
( select oracle_username from system.fnd_oracle_userid
where read_only_flag in ('A','E','B') )
and tab.temporary = 'N'
and tab.secondary = 'N'
/* not an application-managed dynamic table */
and not regexp_like(tab.table_name, '^AQ\$', 'c')
and not regexp_like(tab.table_name, '^AW\$', 'c')
and not regexp_like(tab.table_name, '^MLOG\$', 'c')
and not regexp_like(tab.table_name, '^BSC_DI_[0-9_]+$', 'c')
and not regexp_like(tab.table_name, '^BSC_D_.+$', 'c')
and not regexp_like(tab.table_name, '^FA_ARCHIVE_ADJUSTMENT_.+$', 'c')
and not regexp_like(tab.table_name, '^FA_ARCHIVE_DETAIL_.+$', 'c')
and not regexp_like(tab.table_name, '^FA_ARCHIVE_SUMMARY_.+$', 'c')
and not regexp_like(tab.table_name, '^GL_DAILY_POST_INT_.+$', 'c')
and not regexp_like(tab.table_name, '^GL_INTERCO_BSV_INT_[0-9]+$', 'c')
and not regexp_like(tab.table_name, '^GL_MOVEMERGE_BAL_[0-9]+$', 'c')
and not regexp_like(tab.table_name, '^GL_MOVEMERGE_INTERIM_[0-9]+$', 'c')
and not regexp_like(tab.table_name, '^XLA_GLT_[0-9]+$', 'c')
and not regexp_like(tab.table_name, '^ICX_POR_C[0-9]+.*$', 'c')
and not regexp_like(tab.table_name, '^ICX_POR_UPLOAD_[0-9]+.*$', 'c')
and not regexp_like(tab.table_name, '^IGI_SLS_[0-9]+$', 'c')
and not regexp_like(tab.table_name, '^JTF_TAE_[0-9]+.*$', 'c')
and not regexp_like(tab.table_name, '^JTY_[0-9]+_.*$', 'c')
and not regexp_like(tab.table_name, '^ZPBDATA[0-9]+_EXCPT_T$', 'c')
and not regexp_like(tab.table_name, '^ZX_DATA_UPLOAD_.*$', 'c')
/* not an AD infrastructure table table */
and tab.table_name not in
( 'AD_DEFERRED_JOBS',
'AD_TABLE_INDEX_INFO',
'FND_INSTALL_PROCESSES' )
and not exists /* not a queue table */
( select qt.owner, qt.queue_table
from dba_queue_tables qt
where qt.owner = tab.owner
and qt.queue_table = tab.table_name )
and not exists /* not an MV container table */
( select mv.owner, mv.container_name
from dba_mviews mv
where mv.owner = tab.owner
and mv.container_name = tab.table_name )
and exists /* has apps synonym to base table */
( select syn.table_owner, syn.table_name
from dba_synonyms syn
where syn.table_owner = tab.owner
and syn.table_name = tab.table_name
and syn.owner = ad_zd.apps_schema )
and not exists /* not an obsolete table */
( select
fou.oracle_username owner
, aoo.object_name object_name
from
system.fnd_oracle_userid fou
, fnd_product_installations fpi
, ad_obsolete_objects aoo
where fpi.application_id = aoo.application_id
and fou.oracle_id = fpi.oracle_id
and fou.oracle_username = tab.owner
and aoo.object_name = tab.table_name
and aoo.object_type = 'TABLE' )
order by tab.owner, tab.table_name;
select ev.owner table_owner, ev.table_name
from dba_editioning_views ev
where ev.owner in
( select oracle_username from system.fnd_oracle_userid
where read_only_flag in ('A','E','B') )
and ( exists /* uncoverted synonyms */
( select syn.synonym_name
from dba_synonyms syn
where syn.owner in
( select oracle_username from system.fnd_oracle_userid
where read_only_flag in ('A', 'B', 'C', 'E', 'U') )
and syn.table_owner = ev.owner
and syn.table_name = ev.table_name ) or
exists /* unmoved triggers */
( select trg.trigger_name
from dba_triggers trg
where trg.owner in
( select oracle_username from system.fnd_oracle_userid
where read_only_flag in ('A', 'B', 'C', 'E', 'U') )
and trg.trigger_name not like '%$%' /* system trigger */
and trg.crossedition = 'NO'
and trg.table_owner = ev.owner
and trg.table_name = ev.table_name )
)
order by table_owner, table_name;
select syn.owner owner, syn.synonym_name name
from dba_synonyms syn
where syn.table_owner = x_table_owner
and syn.table_name = ad_zd_table.ev_view(x_table_name)
and syn.owner <> 'PUBLIC';
select tab.owner, tab.table_name
into l_table_owner, l_table_name
from dba_tables tab
where tab.owner = x_table_owner
and tab.table_name = x_table_name;
procedure UPDATE_4FCET(
X_TABLE_OWNER varchar2,
X_TABLE_NAME varchar2,
X_TRIGGER_NAME varchar2,
X_COLUMN_NAME varchar2,
X_EV_NAME varchar2)
is
C_MODULE varchar2(80) := c_package || 'update_4fcet';
select
trg.owner owner
, trg.trigger_name trigger_name
, trg.status status
from
dba_triggers trg
where trg.owner <> user
and trg.table_owner = x_table_owner
and trg.table_name = x_table_name
order by 1, 2;
l_stmt := 'update /*+ rowid (tbl) */ '||
x_table_owner||'.'||nvl(x_ev_name, x_table_name)||' tbl set '||
x_column_name||'='||x_column_name||' where rowid between :start_id and :end_id ';
log(c_module, 'STATEMENT', 'Parallel Update for '||x_trigger_name);
log(c_module, 'WARNING', 'Update failed, status='||to_char(l_status)||', Retrying...');
error(c_module, 'Update failed, status = '||to_char(l_status));
log(c_module, 'STATEMENT', 'Successful Parallel Update, dropping task: '||l_task_name);
end update_4fcet;
select
tord.referenced_trigger_owner ref_trg_owner
, tord.referenced_trigger_name ref_trg_name
, rtrg.status trg_status
from
dba_trigger_ordering tord
, dba_triggers rtrg
where tord.trigger_owner = x_owner
and tord.trigger_name = x_name
and tord.ordering_type = 'FOLLOWS'
and rtrg.owner(+) = tord.referenced_trigger_owner
and rtrg.trigger_name(+) = tord.referenced_trigger_name
order by 1, 2;
select
tord.referenced_trigger_owner ref_trg_owner
, tord.referenced_trigger_name ref_trg_name
from
dba_trigger_ordering tord
start with
tord.trigger_owner = x_owner
and tord.trigger_name = x_name
and tord.ordering_type = 'FOLLOWS'
connect by
tord.trigger_owner = prior tord.referenced_trigger_owner
and tord.trigger_name = prior tord.referenced_trigger_name
and tord.ordering_type = 'FOLLOWS';
select trg.status, trg.crossedition, trg.table_owner, trg.table_name
into l_status, l_crossedition, l_table_owner, l_table_name
from dba_triggers trg
where trg.crossedition in ('FORWARD', 'REVERSE')
and trg.owner = c_cet_owner
and trg.trigger_name = x_cet_name;
select col.column_name into l_upd_column
from dba_tab_columns col
where col.owner = l_table_owner
and col.table_name = l_table_name
and col.column_id = 1;
'" before insert or update of '||l_upd_column||' on "'||
l_table_owner||'"."'||l_table_name||
'" for each row '||l_crossedition||' crossedition begin null; end; ';
select 'Y' into l_exists from dual where exists
( select tord.trigger_name
from dba_trigger_ordering tord
where tord.referenced_trigger_owner = c_cet_owner
and tord.referenced_trigger_name = x_cet_name
and tord.ordering_type = 'FOLLOWS' );
log(c_module, 'PROCEDURE', 'end: '|| c_cet_owner || '.' || x_cet_name || ' - update deferred');
select x.view_column_name into l_upd_column from
( select evc.view_column_name, count(idc.index_name), col.data_length
from dba_tab_columns col, dba_ind_columns idc, dba_editioning_view_cols evc
where col.owner = l_table_owner
and col.table_name = l_table_name
and evc.owner = col.owner
and evc.view_name = l_upd_ev_name
and evc.table_column_name = col.column_name
and idc.table_owner(+) = col.owner
and idc.table_name(+) = col.table_name
and idc.column_name(+) = col.column_name
group by evc.view_column_name, col.data_length
order by count(idc.index_name), col.data_length ) x
where rownum = 1;
select x.column_name into l_upd_column from
( select col.column_name, count(idc.index_name), col.data_length
from dba_tab_columns col, dba_ind_columns idc
where col.owner = l_table_owner
and col.table_name = l_table_name
and idc.table_owner(+) = col.owner
and idc.table_name(+) = col.table_name
and idc.column_name(+) = col.column_name
group by col.column_name, col.data_length
order by count(idc.index_name), col.data_length ) x
where rownum = 1;
ad_zd_table.update_4fcet(l_table_owner, l_table_name, l_upd_cet_name, l_upd_column, l_upd_ev_name);
log(c_module, 'PROCEDURE', 'end - update');
select idc.column_name
from dba_ind_columns idc
where idc.index_owner = x_owner
and idc.index_name = x_name
order by column_position;
select idx.index_name
into l_revised_name
from dba_indexes idx
where idx.owner = x_index_owner
and idx.index_name = ad_zd_table.revised_index_name(x_index_name);
select table_owner, table_name,
'create '||
decode(index_type,
'NORMAL', decode(uniqueness, 'UNIQUE', 'UNIQUE', ''),
index_type)||
' index "'||owner||'"."'||ad_zd_table.revised_index_name(index_name)||'"'||
' on "'||table_owner||'"."'||table_name||'"' I_HEADER,
decode(nvl(tablespace_name,'???'),'???','','tablespace '||tablespace_name)||
' storage (initial '||nvl(initial_extent, 128*1024)/1024||'K '||
'next '||nvl(next_extent, 128*1024)/1024||'K)'
into l_table_owner, l_table_name, l_pre_stmt, l_post_stmt
from dba_indexes
where owner = x_index_owner
and index_name = x_index_name;
select idx.owner, idx.index_name, idx.index_type, idx.partitioned
from dba_indexes idx
where idx.owner in
( select oracle_username from system.fnd_oracle_userid
where read_only_flag in ('A', 'B', 'E', 'U') )
and idx.owner = idx.table_owner
and idx.table_owner = x_table_owner
and idx.table_name = x_table_name
and regexp_like(idx.index_name, ad_zd_table.original_index_regexp, 'c')
and (
(
/* Revised Index does not exist and Original Index is out of date */
not exists
( select idt.index_name
from dba_indexes idt
where idt.owner = idx.owner
and idt.index_name = ad_zd_table.revised_index_name(idx.index_name) )
and exists
( select col.column_name
from dba_tab_columns col, dba_ind_columns idc
where col.owner = idx.table_owner
and col.table_name = idx.table_name
and idc.index_owner = idx.owner
and idc.index_name = idx.index_name
and ad_zd_table.ev_view_column(idc.column_name)=ad_zd_table.ev_view_column(col.column_name)
and col.column_name > idc.column_name )
)
or
(
/* Revised Index exists, but is out of date */
exists
( select idt.index_name
from dba_indexes idt
where idt.owner = idx.owner
and idt.index_name = ad_zd_table.revised_index_name(idx.index_name) )
and exists
( select col.column_name
from dba_tab_columns col, dba_ind_columns idc
where col.owner = idx.table_owner
and col.table_name = idx.table_name
and idc.index_owner = idx.owner
and idc.index_name = ad_zd_table.revised_index_name(idx.index_name)
and ad_zd_table.ev_view_column(idc.column_name)=ad_zd_table.ev_view_column(col.column_name)
and col.column_name > idc.column_name )
)
)
order by 1, 2;
select
ridx.owner owner
, ridx.index_name revised_index
, ridx.table_owner table_owner
, ridx.table_name table_name
, oidx.index_name original_index
, con.constraint_name constraint_name
from
dba_indexes ridx
, dba_indexes oidx
, dba_constraints con
where ridx.owner in
( select oracle_username from system.fnd_oracle_userid
where read_only_flag in ('A', 'B', 'E', 'U') )
and ridx.table_owner = x_table_owner
and ridx.table_name = x_table_name
and regexp_like(ridx.index_name, ad_zd_table.revised_index_regexp, 'c')
and oidx.owner(+) = ridx.owner
and oidx.index_name(+) = ad_zd_table.original_index_name(ridx.index_name)
and con.owner(+) = ridx.table_owner
and con.table_name(+) = ridx.table_name
and con.index_owner(+) = ridx.owner
and con.index_name(+) = ad_zd_table.original_index_name(ridx.index_name)
order by 1, 2;
select owner, name, status
from ad_patched_tables
where status in ('N', 'U');
update ad_patched_tables
set status = 'C'
where owner = tablerec.owner
and name = tablerec.name;
select col.owner, col.table_name, col.column_name, col.nullable, col.data_default
from dba_tables tab
, dba_tab_columns col
, dba_editioning_views ev
where tab.owner in
( select oracle_username
from system.fnd_oracle_userid
where read_only_flag in ('A','E','B') )
and col.owner = tab.owner
and col.table_name = tab.table_name
and col.owner = x_owner
and col.table_name = x_table_name
and col.column_name <> 'ZD_EDITION_NAME'
and ev.owner = col.owner
and ev.view_name = substrb(col.table_name, 1, 29)||'#'
and not exists
( select evc.table_column_name
from dba_editioning_view_cols evc
where evc.owner = ev.owner
and evc.view_name = ev.view_name
and evc.table_column_name = col.column_name )
order by col.owner, col.table_name, col.column_name;
select cc.constraint_name
from dba_cons_columns cc
where cc.owner = x_owner
and cc.table_name = x_table_name
and cc.column_name = x_column_name;
delete from ad_patched_tables
where owner = x_table_owner
and name = x_table_name;
select trg.owner, trg.trigger_name
from dba_triggers trg
where trg.crossedition in ('FORWARD', 'REVERSE')
and trg.table_owner = nvl(x_table_owner, trg.table_owner)
and trg.table_name = nvl(x_table_name, trg.table_name)
order by trg.owner, trg.trigger_name;
select col.owner, col.table_name, col.column_name
from dba_tab_columns col
, dba_editioning_views ev
where ev.owner in
( select oracle_username
from system.fnd_oracle_userid
where read_only_flag in ('A','E','B')
)
and ev.owner = nvl(x_table_owner, col.owner)
and ev.table_name = nvl(x_table_name, col.table_name)
and col.column_name <> 'ZD_EDITION_NAME'
and ev.owner = col.owner
and ev.table_name = col.table_name
and not exists
( select evc.table_column_name
from dba_editioning_view_cols evc
where evc.owner = ev.owner
and evc.view_name = ev.view_name
and evc.table_column_name = col.column_name
)
order by col.owner, col.table_name, col.column_name;
select ev.owner, ev.table_name
from dba_unused_col_tabs uct, dba_editioning_views ev
where uct.owner in
( select oracle_username
from system.fnd_oracle_userid
where read_only_flag in ('A','E','B') )
and uct.owner = nvl(x_table_owner, uct.owner)
and uct.table_name = nvl(x_table_name, uct.table_name)
and ev.owner = uct.owner
and ev.table_name = uct.table_name
order by 1, 2;
select idx.owner, idx.index_name,
con.owner table_owner, con.table_name, con.constraint_name
from
dba_indexes idx
, dba_constraints con
where idx.owner in
( select oracle_username from system.fnd_oracle_userid
where read_only_flag in ('A', 'B', 'E', 'U') )
and regexp_like(idx.index_name, ad_zd_table.revised_index_regexp, 'c')
and con.owner(+) = idx.table_owner
and con.table_name(+) = idx.table_name
and con.index_name(+) = idx.index_name
order by 1, 2;
select col.owner, col.table_name, col.column_name, col.data_default
from dba_tab_columns col
, dba_editioning_views ev
where col.owner in
( select oracle_username
from system.fnd_oracle_userid
where read_only_flag in ('A','E','B') )
and col.nullable = 'N'
and col.table_name not like '%#'
and (col.default_length is null or col.default_length = 4)
and ev.owner = col.owner
and ev.view_name = substrb(col.table_name, 1, 29)||'#'
and not exists
( select evc.table_column_name
from dba_editioning_view_cols evc
where evc.owner = ev.owner
and evc.view_name = ev.view_name
and evc.table_column_name = col.column_name )
order by col.owner, col.table_name, col.column_name;
delete from ad_patched_tables;
select syn.owner owner, syn.synonym_name name
from dba_synonyms syn
where syn.table_owner = x_table_owner
and syn.table_name = x_ev_name
and syn.owner <> 'PUBLIC';
select count(1)
into l_count
from dba_tables tab
where tab.owner = l_table_owner
and tab.table_name = x_table_name;