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=======
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
