[Script]Re-Compile All Invalid Package/Package Body

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=======