[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select s.table_owner, s.table_name
into l_owner, l_view_name
from dba_synonyms s
where owner = ad_zd.apps_schema
and synonym_name = x_synonym_name;
select 1 into l_dummy
from dba_source
where owner = ad_zd.apps_schema
and name = ad_zd_seed.eds_function(x_table_name)
and type = 'FUNCTION'
and instr(text, ad_zd.get_edition('PATCH')) <> 0;
select owner, trigger_name
from dba_triggers
where table_owner = x_tab_owner
and table_name in (x_tab_name, ad_zd_table.ev_view(x_tab_name))
and trigger_name <> ad_zd_seed.eds_trigger(x_tab_name)
and crossedition = 'NO';
l_stmt := 'update '||x_owner||'."'||x_table_name||'"'||
' set ZD_EDITION_NAME = '''||x_edition_name||''''||
' where ZD_EDITION_NAME != '''||x_edition_name||'''';
select i.owner, i.index_name
from dba_indexes i
where i.table_owner = x_tab_owner
and i.table_name = x_tab_name
and i.uniqueness = 'UNIQUE'
and i.index_type <> 'LOB'
and not exists ( select null
from dba_ind_columns c
where c.index_owner = i.owner
and c.index_name = i.index_name
and c.column_name = 'ZD_EDITION_NAME' );
select constraint_name, constraint_type
from dba_constraints o
where owner = x_tab_owner
and table_name = x_tab_name
and constraint_type in ('U','P')
and not exists ( select null
from dba_cons_columns i
where i.owner = o.owner
and i.constraint_name = o.constraint_name
and i.column_name = 'ZD_EDITION_NAME' );
select trigger_body
from dba_triggers
where table_owner = x_tab_owner
and table_name = x_tab_name
and trigger_name = x_trig_name;
' select property_value into l_default'||
' from database_properties where property_name = ''DEFAULT_EDITION'';'||
' if INSERTING then '||
' :new.zd_edition_name := '''||x_edition_name||''';'||
' before insert or update or delete on "'||x_table_owner||'"."'||l_ev_name||'"'||
' for each row '|| l_trig_body;
select policy_name
from dba_policies
where object_owner = x_tab_owner
and object_name = ad_zd_table.ev_view(x_tab_name)
and policy_name = ad_zd_seed.eds_policy;
statement_types => 'select, update, delete, index');
** The trigger will sync updates from the RUN edition to the PATCH edition
**
** X_COPY_DATA: true to copy the Run Edition seed data to the Patch Edition
** false to skip the copy (when it was already done)
*/
procedure CREATE_SYNC(
X_TABLE_OWNER in varchar2,
X_TABLE_NAME in varchar2,
X_COPY_DATA in boolean)
is
C_MODULE varchar2(80) := c_package||'create_sync';
select column_name
from dba_tab_columns
where owner = x_tab_owner
and table_name = x_tab_name
and column_name <> 'ZD_EDITION_NAME'
order by column_id;
select
up.index_name
, ic.column_name
, nvl(col.nullable, 'Y') nullable
from
( select i.owner, i.index_name, c.constraint_name
from dba_indexes i, dba_constraints c
where i.table_owner = x_owner
and i.table_name = x_tab_name
and i.uniqueness = 'UNIQUE'
and i.index_type = 'NORMAL' /* exclude other index types */
and c.owner(+) = i.table_owner
and c.table_name(+) = i.table_name
and c.index_owner(+) = i.owner
and c.index_name(+) = i.index_name
and c.constraint_type(+) = 'P'
and i.index_name not like '%~%' /*exclude revised indexes created by a patch */
and rownum = 1
order by 3,1,2 /* puts PK row first, if it exists */) up,
dba_ind_columns ic, dba_tab_columns col
where ic.index_owner = up.owner
and ic.index_name = up.index_name
and ic.column_name <> 'ZD_EDITION_NAME'
and ic.table_owner = x_owner
and ic.table_name = x_tab_name
and col.owner = x_owner
and col.table_name = x_tab_name
and col.column_name = ic.column_name
order by ic.column_position;
log(c_module, 'EVENT', 'Copy Seed Data using insert-select: '||x_table_name);
exec('insert into "'||x_table_owner||'"."'||ad_zd_table.ev_view(x_table_name)||
'" select * from "'||x_table_owner||'"."'||ad_zd_table.ev_view(x_table_name)||'"',
c_module);
||NL||' for insert or update or delete on '||x_table_owner||'.'||x_table_name
||NL||' forward crossedition '||l_disable||' compound trigger '
||NL||' type DATA_T is table of '||x_table_owner||'.'||x_table_name||'%ROWTYPE index by simple_integer;'
||NL||' if inserting then'
||NL|| l_new_key_stmt
||NL||' else'
||NL|| l_old_key_stmt
||NL||' end if;'
||NL||' if inserting or updating then'
||NL|| l_save_stmt
||NL||' end if;'
||NL||' if inserting then'
||NL||' for j IN 1..l_key.count loop'
||NL||' begin '
||NL||' insert into '||x_table_owner||'.'||x_table_name
||NL||' values l_data(j);'
||NL||' update '||x_table_owner||'.'||x_table_name||' set row = l_data(j)'
||NL||' '||l_where_stmt||' and zd_edition_name=l_edition;'
||NL||' insert into '||x_table_owner||'.'||x_table_name
||NL||' values l_data(j);'
||NL||' update '||x_table_owner||'.'||x_table_name||' set row = l_data(j)'
||NL||' '||l_where_stmt||' and zd_edition_name=l_edition;'
||NL||' delete from '||x_table_owner||'.'||x_table_name
||NL|| l_where_stmt||' and zd_edition_name=l_edition;'
select to_number(value) into v_parallel
from v$parameter where name='parallel_max_servers';
select 'Y' into l_exists from dual
where exists
( select syn.object_name from dba_objects_ae syn
where syn.owner = ad_zd.apps_schema
and syn.object_name = x_table_name
and syn.object_type = 'SYNONYM'
and syn.edition_name =
( select max(ed.edition_name) from dba_objects_ae ed
where ed.owner = syn.owner
and ed.object_name = syn.object_name
and ed.edition_name < ad_zd.get_edition ) );
select 'Y' into l_exists from dual
where exists
( select column_name
from dba_tab_columns
where owner = l_table_owner
and table_name = l_table_name
and data_type = 'LONG' );
select 'Y' into l_exists
from user_triggers
where trigger_name = ad_zd_seed.eds_trigger(l_table_name)
and table_owner = l_table_owner
and table_name = ad_zd_table.ev_view(l_table_name);
select i.owner, i.index_name
from dba_indexes i
where i.table_owner = x_tab_owner
and i.table_name = x_tab_name
and i.uniqueness = 'UNIQUE'
and i.index_type <> 'LOB'
and exists ( select null
from dba_ind_columns c
where c.index_owner = i.owner
and c.index_name = i.index_name
and c.column_name = 'ZD_EDITION_NAME' );
select constraint_name, constraint_type
from dba_constraints o
where owner = x_tab_owner
and table_name = x_tab_name
and constraint_type in ('U','P')
and exists ( select null
from dba_cons_columns i
where i.owner = o.owner
and i.constraint_name = o.constraint_name
and i.column_name = 'ZD_EDITION_NAME' );
select object_owner, object_name, policy_name, function from dba_policies
where object_owner = x_tab_owner
and object_name in (x_tab_name, ad_zd_table.ev_view(x_tab_name))
and upper(policy_name) like '%ZD_SEED';
** seed data table a loader will insert/delete/update
** data in.
**
** 1). Create new trigger to populate ZD_EDITION_NAME
** 2). Create a copy of the data
** 3). Create new VPD POLICY to restrict sql to that new data
** 4). Create synchronization FCET
** 5). Commit
**
*/
procedure PREPARE(X_TABLE_NAME in varchar2)
is
C_MODULE varchar2(80) := c_package||'prepare';
select text
from dba_source
where owner = x_owner
and name = x_function
and type = 'FUNCTION'
and instr(text,x_edition) <> 0;
'select zd_edition_name'||
' from '||x_table_owner||'.'||ad_zd_table.ev_view(x_table_name)||
' where rownum=1';
'delete from '||x_table_owner||'.'||x_table_name||
' where zd_edition_name <> '''||l_seed_edition||'''';
** Cleanup - delete old seed data rows
**
** NOTE: old triggers and policy functions will be handled by
** central edition manager cleanup
*/
procedure CLEANUP(X_TABLE_NAME in varchar2 default NULL)
is
C_MODULE varchar2(80) := c_package||'cleanup';
select col.owner, col.table_name
from
dba_tab_columns col
, user_objects obj
where col.owner in
( select oracle_username from system.fnd_oracle_userid
where read_only_flag in ('A','E') )
and col.table_name not like '%#'
and col.column_name = 'ZD_EDITION_NAME'
and obj.object_name = ad_zd_seed.eds_function(col.table_name)
and obj.object_type = 'FUNCTION'
and obj.edition_name = sys_context('userenv', 'current_edition_name')
and obj.edition_name <> 'ORA$BASE'
and exists
( select src.line from user_source src
where src.name = obj.object_name
and src.type = obj.object_type
and src.text like '%'||obj.edition_name||'%' );
select s.table_owner, s.table_name
into l_table_owner, l_table_name
from dba_synonyms s
where s.owner = ad_zd.apps_schema
and s.synonym_name = x_table_name;
l_table_name_tab.delete;
l_table_owner_tab.delete;
select col.owner, col.table_name
from
dba_tab_columns col
, user_objects_ae obj
where col.owner in
( select oracle_username from system.fnd_oracle_userid
where read_only_flag in ('A','E') )
and col.table_name not like '%#'
and col.column_name = 'ZD_EDITION_NAME'
and obj.object_name = ad_zd_seed.eds_function(col.table_name)
and obj.object_type = 'FUNCTION'
and obj.edition_name > sys_context('userenv', 'current_edition_name')
and exists
( select src.text from user_source_ae src
where src.edition_name = obj.edition_name
and src.name = obj.object_name
and src.type = obj.object_type
and src.text like '%'||obj.edition_name||'%' );