[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into
ad_parallel_compile (obj#,
owner,
name,
type,
depend_level,
worker_number,
status,
sys_stat)
select /*+ leading(o) */
o.obj#,
u.name,
o.name,
decode(o.type#, 4,'VIEW',
7,'PROCEDURE',
8,'FUNCTION',
9,'PACKAGE',
11,'PACKAGE BODY',
12,'TRIGGER',
29,'JAVA CLASS',
42,'MATERIALIZED VIEW',
'UNDEFINED'),
0,
0,
'INVALID',
o.status
from
sys.obj$ o,
sys.user$ u
where
u.user# = o.owner#
and u.name = sName
and o.name not like 'BIN$%'
and o.type# in (4,7,8,9,11,12,29,42)
and o.status >1;
insert into
ad_parallel_compile (obj#,
owner,
name,
type,
depend_level,
worker_number,
status,
sys_stat)
select /*+ leading(o) */
o.obj#,
u.name,
o.name,
decode(o.type#, 4,'VIEW',
7,'PROCEDURE',
8,'FUNCTION',
9,'PACKAGE',
11,'PACKAGE BODY',
12,'TRIGGER',
29,'JAVA CLASS',
42,'MATERIALIZED VIEW',
'UNDEFINED'),
0,
0,
'INVALID',
o.status
from
sys.obj$ o,
sys.user$ u
where
u.user# = o.owner#
and u.name = sName
and o.name not like 'BIN$%'
and o.type# in (4,7,8,9,12,29,42)
and o.status > 1;
raise_application_error(-20000,SQLERRM||' while inserting into AD_PARALLEL_COMPILE in '||
'AD_PARALLEL_COMPILE_PKG.GET_INVALID()');
delete from ad_parallel_compile
where obj# in
(select /*+ leading(t) use_nl(o) */
o.obj#
from
sys.obj$ o,
sys.trigger$ t,
sys.user$ u
where
u.user# = o.owner#
and u.name = sName
and o.obj# = t.obj#
and o.type# = 12
and o.status = 3
and t.enabled = 0);
procedure delete_errobjs
(schema_to_compile in varchar2)
is
-- |*=====================================================================*
-- | DESCRIPTON
-- | Delete the objects from the parallel compile table
-- | that are also in the dba_errors table. There is no
-- | point in trying to recompile these objects, as they clearly
-- | have errors. We just want to catch any potentially valid
-- | objects that slipped through the cracks...
-- |
-- | USAGE
-- | Specify the schema name to delete the error ojjects from.
-- |
-- | EXAMPLE
-- | delete_errobjs('APPS');
delete from ad_parallel_compile
where obj# in
(select
o.obj#
from
sys.obj$ o,
sys.error$ e,
sys.user$ u
where
u.name = sName
and o.owner# = u.user#
and e.obj# = o.obj#
and e.sequence# = 1);
end delete_errobjs;
procedure update_done
is
begin
--
-- possible corner-case error: object changes from one invalid status
-- to another invalid status. I hope this doesn't happen
--
update ad_parallel_compile
set status = 'VALID'
where obj# in
(select pc.obj#
from ad_parallel_compile pc, sys.obj$ o
where pc.obj# = o.obj#
and pc.sys_stat <> o.status);
end update_done;
update ad_parallel_compile
set depend_level = tree_depth
where obj# in
(select d.d_obj#
from sys.dependency$ d
where d.p_obj# in
(select p.obj#+0
from ad_parallel_compile p
where p.owner = nvl(sName, p.owner)
and p.depend_level = tree_depth - 1)
and d.d_owner# = (select user#
from sys.user$ where name = sName));
update ad_apps_dependencies d
set obj# =
(select object_id
from dba_objects
where owner = sName
and object_name = d.name
and object_type = d.type);
update ad_parallel_compile c
set c.depend_level =
(select d.depend_level
from ad_apps_dependencies d
where d.obj# = c.obj#)
where exists (
select 'x'
from ad_apps_dependencies d
where d.obj# = c.obj#);
'select dbms_java.longname(:object_name) from dual'
INTO l_complete_name
USING object_name;
select status
into l_status
from dba_objects
where owner = sName
and object_name = object_name_to_check
and object_type = object_type_to_check
and status = 'INVALID';
select * from ad_parallel_compile
where owner = c_schema_to_compile
and (c_distribute_java = 'Y'
or
(c_distribute_java = 'N'
and
type <> 'JAVA CLASS')
)
order by depend_level,
decode(type, 'PACKAGE',1,'VIEW',2,'PACKAGE BODY',4,3),
name;
update ad_parallel_compile
set worker_number = 1
where owner = sName
and type = 'JAVA CLASS';
update ad_parallel_compile
set worker_number = counter
where obj# = csrec.obj#;
update ad_parallel_compile
set worker_number = log_worker_num
where upper(owner) = upper(schema_to_compile);
select type, substr(name,1,30) name
from ad_parallel_compile
where owner = c_target_schema
and worker_number = c_worker_number
and status = 'INVALID'
order by depend_level,
decode(type, 'PACKAGE',1,'VIEW',2,'PACKAGE BODY',4,3),
name;
insert into
ad_parallel_compile_errors (owner,
worker_number,
timestamp,
type,
text)
values (l_target_schema,
tmp_worker,
timestamp,
'1 SQL TEXT',
statement);
insert into
ad_parallel_compile_errors (owner,
worker_number,
timestamp,
type,
text)
values (l_target_schema,
tmp_worker,
timestamp,
'2 ORACLE_ERROR',
sql_error);
insert into
ad_parallel_compile_errors (owner,
worker_number,
timestamp,
type,
text)
values (l_target_schema,
tmp_worker,
timestamp,
'3 ERROR_BUF',
substr(ad_apps_private.error_buf,
1,1996));