假如指定不正確，則會(huì)報(bào)以下的錯(cuò)誤 SQL> dbms_ddl.alter_compile('PROCEDURE','MYUSER','access_rights'); BEGIN dbms_ddl.alter_compile('PROCEDURE','MYUSER','access_rights'); END; * ERROR at line 1: ORA-20000: Unable to compile PROCEDURE "MYUSER"."access_rights", insufficient privileges or does not exist ORA-06512: at "SYS.DBMS_DDL", line 68 ORA-06512: at line 1
因此假如你創(chuàng)建如下的對(duì)象: CREATE or REPLACE PROCEDURE "My_Object" IS BEGIN ... 得使用: DBMS_DDL.ALTER_COMPILE('PROCEDURE','MYUSER','My_Object');
set termout on set serverout on DECLARE cursor cur_invalid_objects is select object_name, object_type from user_objects where status='INVALID'; rec_columns cur_invalid_objects%ROWTYPE; err_status NUMERIC; BEGIN dbms_output.enable(10000); open cur_invalid_objects; loop fetch cur_invalid_objects into rec_columns; EXIT WHEN cur_invalid_objects%NOTFOUND;
dbms_output.put_line ('Recompiling ' rec_columns.object_type ' ' rec_columns.object_name); dbms_ddl.alter_compile(rec_columns.object_type,NULL,rec_columns.object_name); end loop; close cur_invalid_objects; EXCEPTION When others then begin err_status := SQLCODE; dbms_output.put_line(' Recompilation failed : ' SQLERRM(err_status));
if ( cur_invalid_objects%ISOPEN) then CLOSE cur_invalid_objects; end if;
