-
Expert Member
Compiling Invalid Objects
I am using the following code to compile invalid objects. Some times i need to run this code four to five times. Is there any better code to compile invalid objects at a time? set head off set feedback off set pagesize 0 spool com.sql select 'alter '|| object_type||' '|| object_name ||' compile ; ' from user_objects where status = 'invalid' and object_type != 'package body' order by object_type / select 'alter package '|| object_name ||' compile body ; ' from user_objects where status = 'invalid' and object_type = 'package body' order by object_type / spool off @com.sql set head on set feedback on set pagesize 100 /
-
Expert Member
Re: Compiling Invalid Objects
EXEC DBMS_UTILITY.COMPILE_SCHEMA( 'schema-name' );
-
Expert Member
Re: Compiling Invalid Objects
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;
exception
when others then
null;
end;
end;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules