A plsql procedure written using dbms_ddl package and all_objects table from sys schema where we have used one procedure from dbms_ddl package known as alter_compile which takes four arguments.
1. Object_type - Type of object (i.e. function, procedure, package)
2. Owner - User/ Schema name under which object exists
3. Object Name - Function / procedure name
4. Reuse settings (default false)
This program runs a loop navigation through all the objects and checking for whether it is
a function, procedure or package / package bodies, compiling it and error takes place printing error
to console.
Here is the program.
create or replace procedure CompileAllObjects is
cursor rec is select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects
where OBJECT_TYPE in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODIES','TRIGGER')
and owner like &<name="Object owner"
hint="The object owner (wildcards allowed)"
type="string"
default="select user from dual"
ifempty="%"
list="select username from all_users order by username">
and OBJECT_TYPE like &<name="Object type"
hint="The object type"
type="string"
default="All"
ifempty="All"
list="%, All, PROCEDURE, Procedures, FUNCTION, Functions, PACKAGE%, Packages, TYPE%, Types, TRIGGER, Triggers, TABLE, Tables, INDEX, Indexes, SEQUENCE, Sequences, SYNONYM, Synonyms"
description="yes">;
begin
for i in rec loop
begin
dbms_ddl.alter_compile(i.OBJECT_TYPE,i.OWNER,i.OBJECT_NAME,false);
exception
when others then
dbms_output.put_line('Error cannot compile : '||i.OBJECT_TYPE||' '||i.OBJECT_NAME||' Error - '||sqlerrm());
end;
end loop;
end CompileAllObjects;
No comments:
Post a Comment