How to Re-Compile All Invalid for Oracle EBS Package/Package Body
set serveroutput on size 1000000
declare
sql_statement varchar2(200);
cursor_id number;
ret_val number;
CURSOR cur_invalid IS
select object_type, owner, object_name
from sys.dba_objects o
where o.status = \'INVALID\'
and o.object_type in (\'PACKAGE\', \'PACKAGE BODY\')
AND object_name LIKE \'PO%\';--重新编译PO开头的Package
begin
dbms_output.put_line(chr(0));
dbms_output.put_line(\'Re-compilation of Invalid Objects\');
dbms_output.put_line(\'---------------------------------\');
dbms_output.put_line(chr(0));
/*for invalid in (select object_type, owner, object_name
from sys.dba_objects o,
sys.order_object_by_dependency d
where o.object_id = d.object_id(+)
and o.status = \'INVALID\'
and o.object_type in (\'PACKAGE\', \'PACKAGE BODY\',
\'FUNCTION\',
\'PROCEDURE\', \'TRIGGER\',
\'VIEW\')
order by d.dlevel desc, o.object_type) LOOP */
FOR invalid IN cur_invalid LOOP
begin
if invalid.object_type = \'PACKAGE BODY\' then
sql_statement := \'alter package \'||invalid.owner||\'.\'||invalid.object_name||
\' compile body\';
else
sql_statement := \'alter \'||invalid.object_type||\' \'||invalid.owner||\'.\'||
invalid.object_name||\' compile\';
end if;
/* now parse and execute the alter table statement */
cursor_id := dbms_sql.open_cursor;
dbms_sql.parse(cursor_id, sql_statement, dbms_sql.native);
ret_val := dbms_sql.execute(cursor_id);
dbms_sql.close_cursor(cursor_id);
dbms_output.put_line(rpad(initcap(invalid.object_type)||\' \'||
invalid.object_name, 32)||\' : compiled\');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(\'fail to compile \'||invalid.object_type ||\' \' ||invalid.object_name);
END;
end loop;
end;
/
Sample Output
转载请注明出处:http://blog.csdn.net/pan_tian/article/details/7697705
======EOF=======
转载于:http://blog.itpub.net/26687597/viewspace-1203799/