oracle存储过程失效,Oracle自动编译失效对象Job_存储过程实现

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Helvetica}

p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Helvetica; min-height: 13.0px}

p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 9.0px Tahoma; min-height: 11.0px}

--Oracle sys用户创建自动编译未编译成功的对象

create or replace procedure system_recomp is

sql1  user_objects%ROWTYPE;

sql2  user_objects%ROWTYPE;

sql3  user_objects%ROWTYPE;

v_sql1 varchar2(100);

v_sql2 varchar2(100);

v_sql3 varchar2(100);

begin

for sql1 in ( select * from user_objects where status='INVALID' and object_type='TRIGGER') loop

v_sql1 := 'alter trigger '||sql1.object_name||' compile';

execute immediate v_sql1;

end loop;

for sql2 in ( select * from user_objects where status='INVALID' and object_type='FUNCTION') loop

v_sql2 := 'alter FUNCTION '||sql2.object_name||' compile';

execute immediate v_sql2;

end loop;

for sql3 in ( select * from user_objects where status='INVALID' and object_type='PROCEDURE') loop

v_sql3 := 'alter PROCEDURE '||sql3.object_name||' compile';

execute immediate v_sql3;

end loop;

exception when others then

return;

end system_recomp;

/

--批量查询 失效的函数 并逐条执行：

select 'alter FUNCTION '||object_name||' compile;' from user_objects where status='INVALID' and object_type='FUNCTION';

--批量查询 失效的过程 并逐条执行：

select  'alter PROCEDURE '||object_name||' compile;' from user_objects where status='INVALID' and object_type='PROCEDURE';

--批量查询 失效的触发器 并逐条执行：

select 'alter trigger '||object_name||' compile;' from user_objects where status='INVALID' and object_type='TRIGGER';