[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
OBJECT_MARKED_FOR_DELETE exception;
pragma exception_init(object_marked_for_delete, -21700);
select oracle_username into l_applsys
from system.fnd_oracle_userid
where read_only_flag = 'E';
'insert into '||l_applsys||'.ad_zd_logs '||
' (log_sequence, module, message_text, session_id, type, timestamp) '||
' values ('||l_applsys||'.ad_zd_logs_s.nextval, '||
''''||l_module||''', '||
'substrb('''||x_message||''',1, 3900), '||
'sys_context(''USERENV'',''SESSIONID''), '||
''''||x_level||''', SYSDATE) ';
** Update LOGON trigger status.
** - X_STATUS: ENABLE | DISABLE
**
** This is being called from ad_zd.alter_logon_trigger API.
*/
procedure ALTER_LOGON_TRIGGER(X_STATUS varchar2)
is
C_MODULE varchar2(80) := 'alter_logon_trigger';
select oe.object_name edition_name
from
dba_objects_ae oe,
dba_objects_ae re,
database_properties RUN
where run.property_name = 'DEFAULT_EDITION'
/* run edition */
and re.owner = 'SYS'
and re.object_type = 'EDITION'
and re.object_name = run.property_value
/* old edition */
and oe.owner = 'SYS'
and oe.object_type = 'EDITION'
and oe.created < re.created
/* can be used */
and exists
( select 1
from dba_tab_privs
where privilege = 'USE'
and owner = oe.owner
and table_name = oe.object_name
and grantee = 'PUBLIC' );
select property_value into l_default
from database_properties
where property_name = 'DEFAULT_EDITION';
select count(*) into l_count
from dba_tab_privs
where privilege = 'USE'
and owner = l_owner /* Added for GSCC error */
and grantee = 'PUBLIC'
and table_name = x_edition_name;
object_marked_for_delete or
type_not_found or
synonym_does_not_exist
then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
select
oe.created edition_date
, oe.object_name edition_name
, decode(co.object_type,
'TRIGGER', 1,
'PACKAGE BODY', 2,
'TYPE BODY', 3,
'PROCEDURE', 4,
'FUNCTION', 5,
'PACKAGE', 6,
'VIEW', 7,
'SYNONYM', 8,
'TYPE', 9,
/*other*/ 99 ) drop_order
, co.owner
, co.object_type
, co.object_name
from
( select
eusr.edition_name
, eusr.user_name owner
, obj.name object_name
, obj.type#
, decode(obj.type#,
4, 'VIEW',
5, 'SYNONYM',
7, 'PROCEDURE',
8, 'FUNCTION',
9, 'PACKAGE',
10, 'NON-EXISTENT',
11, 'PACKAGE BODY',
12, 'TRIGGER',
13, 'TYPE',
14, 'TYPE BODY',
88, decode((select d.type# from sys.obj$ d
where d.obj# = obj.dataobj#),
4, 'VIEW',
5, 'SYNONYM',
7, 'PROCEDURE',
8, 'FUNCTION',
9, 'PACKAGE',
10, 'NON-EXISTENT',
11, 'PACKAGE BODY',
12, 'TRIGGER',
13, 'TYPE',
14, 'TYPE BODY')) object_type
, obj.namespace namespace
, obj.obj# object_id
from
sys.obj$ obj
, ( select
xusr.user#
, xusr.ext_username user_name
, ed.name edition_name
from
(select * from sys.user$ where type# = 2) xusr
, (select * from sys.obj$
where owner# = 0 and type# = 57) ed
where xusr.spare2 = ed.obj#
union
select
busr.user#
, busr.name user_name
, ed.name edition_name
from
(select * from sys.user$ where type#=1 or user#=1) busr
, (select * from sys.obj$ where owner#=0 and type#=57) ed
where ed.name = 'ORA$BASE' ) eusr
where obj.owner# = eusr.user#
and obj.type# not in (10, 0)
and obj.remoteowner is null ) co
, dba_objects_ae oe /* old edition */
, dba_objects_ae re /* run edition */
, database_properties run /* run edition name */
where run.property_name = 'DEFAULT_EDITION'
/* run edition */
and re.owner = 'SYS'
and re.object_type = 'EDITION'
and re.object_name = run.property_value
/* old edition */
and oe.owner = 'SYS'
and oe.object_type = 'EDITION'
and oe.created < re.created
/* covered object */
and co.edition_name = oe.object_name
and co.object_type in
('SYNONYM', 'VIEW',
'PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY',
'PROCEDURE', 'FUNCTION', 'TRIGGER') /* editioned type */
and exists ( select null
from dba_objects_ae ro /* replacement object */
, dba_objects_ae ne /* newer edition */
where ro.owner = co.owner
and ro.object_type in (co.object_type, 'NON-EXISTENT')
and ro.object_name = co.object_name
and ro.namespace = co.namespace
and ro.edition_name = ne.object_name
and ne.owner = 'SYS'
and ne.object_type = 'EDITION'
and ne.created > oe.created
and ne.created <= re.created )
order by edition_date DESC, drop_order, co.owner, co.object_name;
select count(*)
into l_priv_count
from dba_tab_privs
where privilege = 'USE'
and owner = l_owner /* Added for GSCC error */
and table_name in ( select parent_edition_name from dba_editions);
select oracle_username into l_applsys
from system.fnd_oracle_userid
where read_only_flag = 'E';
l_sql := 'insert into '|| l_applsys ||'.ad_zd_ddl_handler ' ||
'(phase, ddl_id, sql_lob, executed, status) values (' ||
q'['DROP_COVERED_OBJS',]' || l_applsys ||'.ad_zd_ddl_handler_ddl_s.nextval, ' ||
q'['begin sys.ad_zd_sys.drop_covered_object('']' || l_owner||
q'['','']' || l_object_name ||
q'['','']' || l_object_type ||
q'['','']' || l_edition_name ||
q'[''); end;', 'N', 'NOT-EXEC')]' ;
select property_value into l_default_edition
from database_properties
where property_name = 'DEFAULT_EDITION';
select aed.edition_name into l_patch_edition
from all_editions AED
where aed.parent_edition_name = l_default_edition;
select count(*) into l_count
from dba_objects_ae
where owner = x_owner
and object_name = x_object_name
and object_type = x_object_type
and edition_name = l_patch_edition;
** Make 2 passes of dba_objects. During the first pass, select objects with no dependencies
** (i.e LIST1) and in the second pass do the rest (i.e LIST2). This is done for performance
** optimization and to minimize the locking contention while running in parallel workers mode.
**
** LIST1 - contains all the objects with no dependencies. Used during parallel workers mode
** LIST2 - Rest of the objects (i.e objects which have dependencies). Used during parallel workers mode
** LIST3 - All the objects. This is used during execute immediate mode
**
** During the parallel workers mode, utility ids are of no use anymore.
** Ref bug#14026330. Utility ids are converted to PHASES
** LIST PHASE OBJECT_TYPE
** -----------------------------------------------------------------------------
** LIST1 ACTUALIZE_PARENT_OBJS Any editionable object type other
** than PACKAGE BODY, which has no dependencies
** LIST1 ACTUALIZE_PARENT_OBJS PACKAGE BODYs which depends only on the
** respective PACKAGE SPEC
** LIST2 ACTUALIZE_CHILD_OBJS Editionable objects which depends on other objects
*/
PROCEDURE PROCESS_INHERITED_OBJS_LIST(x_list in varchar2,
x_edition in varchar2,
x_execute in boolean default true)
IS
L_STMT varchar2(2000);
select
o.owner
, o.object_name
, o.object_type
from
dba_objects o
where o.edition_name <> x_edition
and o.owner in ( select username from dba_users where editions_enabled = 'Y')
order by o.owner,
decode(o.object_type,
'TYPE', 1, /* most types depend on native or other types */
'SYNONYM', 2, /* synonyms point to tables and types */
'PACKAGE', 3, /* packages can depend on types */
'VIEW', 4, /* views depend on packages and synonyms */
'TYPE BODY', 5,
'PACKAGE BODY', 6,
7), o.object_name;
select * from
(select owner,object_name,object_type
from dba_objects o1
where o1.edition_name <> x_edition
and o1.owner in ( select username from dba_users where editions_enabled = 'Y')
and not exists ( select null from dba_dependencies d
where d.owner = o1.owner
and d.name = o1.object_name
and d.type = o1.object_type
and d.referenced_owner IN (select username from dba_users where editions_enabled = 'Y')
and d.referenced_type IN ('TYPE','SYNONYM','PACKAGE','VIEW','TYPE BODY','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER')
)
union
select owner,object_name,object_type
from dba_objects o2
where o2.edition_name <> x_edition
and o2.owner in (select username from dba_users where editions_enabled = 'Y')
AND o2.object_type = 'PACKAGE BODY'
AND NOT EXISTS ( SELECT NULL
FROM dba_dependencies c
WHERE c.owner = o2.owner
AND c.name = o2.object_name
AND c.TYPE = 'PACKAGE BODY'
AND c.referenced_owner IN (select username from dba_users where editions_enabled = 'Y')
AND c.referenced_type IN ('TYPE','SYNONYM','PACKAGE','VIEW','TYPE BODY','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER')
AND ( (c.referenced_name <> c.name) or (c.referenced_owner <> c.owner) OR (c.referenced_type <> 'PACKAGE'))
)
)
order by decode(object_type,
'PACKAGE BODY', 2,
1), object_name;
select * from
(select owner,object_name,object_type
from dba_objects o1
where o1.edition_name <> x_edition
and o1.owner in ( select username from dba_users where editions_enabled = 'Y')
minus
select owner,object_name,object_type
from dba_objects o2
where o2.edition_name <> x_edition
and o2.owner in ( select username from dba_users where editions_enabled = 'Y')
and not exists ( select null from dba_dependencies d
where d.owner = o2.owner
and d.name = o2.object_name
and d.type = o2.object_type
and d.referenced_owner IN (select username from dba_users where editions_enabled = 'Y')
and d.referenced_type IN ('TYPE','SYNONYM','PACKAGE','VIEW','TYPE BODY','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER')
)
minus
select owner,object_name,object_type
from dba_objects o3
where o3.edition_name <> x_edition
and o3.owner in ( select username from dba_users where editions_enabled = 'Y')
AND o3.object_type = 'PACKAGE BODY'
AND NOT EXISTS ( SELECT NULL
FROM dba_dependencies c
WHERE c.owner = o3.owner
AND c.name = o3.object_name
AND c.TYPE = 'PACKAGE BODY'
AND c.referenced_owner IN (select username from dba_users where editions_enabled = 'Y')
AND c.referenced_type IN ('TYPE','SYNONYM','PACKAGE','VIEW','TYPE BODY','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER')
AND ( (c.referenced_name <> c.name) or (c.referenced_owner <> c.owner) OR (c.referenced_type <> 'PACKAGE'))
)
)
order by decode(object_type,
'TYPE', 1, /* most types depend on native or other types */
'SYNONYM', 2, /* synonyms point to tables and types */
'PACKAGE', 3, /* packages can depend on types */
'VIEW', 4, /* views depend on packages and synonyms */
'TYPE BODY', 5,
'PACKAGE BODY', 6,
7), object_name;
select edition_name
from dba_objects
where owner = x_owner
and object_name = x_object_name
and object_type = x_object_type;
select oracle_username into L_APPLSYS
from system.fnd_oracle_userid
where read_only_flag = 'E';
execute immediate 'insert into '|| L_APPLSYS ||'.AD_ZD_LOGS(LOG_SEQUENCE,MODULE,MESSAGE_TEXT,SESSION_ID,TYPE,TIMESTAMP) values
('|| L_APPLSYS ||'.AD_ZD_LOGS_S.nextval,''ACTUALIZE_ALL'',''DDL : '|| l_stmt ||' ; error = '|| l_errmsg || ''',SYS_CONTEXT(''USERENV'', ''SESSIONID''),''ERROR'',SYSDATE) ';
l_sql := 'insert into '|| l_applsys ||'.ad_zd_ddl_handler ' ||
'(phase, ddl_id, sql_lob, executed, status) values (''' || l_phase ||
q'[',]' || l_applsys ||'.ad_zd_ddl_handler_ddl_s.nextval, ' ||
q'['begin sys.ad_zd_sys.actualize_object('']' || l_owner||
q'['','']' || l_object_name ||
q'['','']' || l_object_type ||
q'[''); end;', 'N', 'NOT-EXEC')]' ;
select
o.owner
, o.object_name
, o.object_type
from
dba_objects o
where o.edition_name <> x_edition
and o.owner in ( select username from dba_users where editions_enabled = 'Y')
and o.object_name like 'AD_ZD%'
and o.object_type in ('PACKAGE','PACKAGE BODY')
order by o.owner,
decode(o.object_type,
'PACKAGE', 1,
'PACKAGE BODY', 2), o.object_name;
select property_value into l_default_edition
from database_properties
where property_name = 'DEFAULT_EDITION';
select aed.edition_name into l_patch_edition
from all_editions AED
where aed.parent_edition_name = l_default_edition;
select oracle_username into L_APPS_SCHEMA
from system.fnd_oracle_userid
where read_only_flag ='U';