Tuesday 1 September 2015

A PLSQL procedure to compile plsql object.


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: