Re: Recompile Invalid Objects - The Brute Solution

dr. Matteo Vitturi wrote:
> Goodnight!
> Is it true? The ultimate solution to recompile all and only invalid objects?
> Give a look to the following link.
>
> http://www.geocities.com/matteo_vitturi/english/plsql.htm
>
> Regards.
> Mat.

hi,

I'm quite happy with the brute solution

create

   procedure recompile_invalid_objects is    cursor inv is

     SELECT object_type, object_name
     FROM user_objects
     WHERE STATUS = 'INVALID';
     anz integer;
     anz_pref integer;
   begin
      select count(*) into anz
      FROM user_objects
      WHERE STATUS = 'INVALID';

      if anz = 0 then return; end if;
      anz_pref := anz + 1;

      while anz_pref > anz and anz > 0 loop
         for inv_rec in inv loop
            if inv_rec.object_type like '% BODY' then
               begin
                  execute immediate 'alter ' || 
substr(inv_rec.object_type, 1, instr(inv_rec.object_type, ' BODY') - 1) ||
                                    ' "' || inv_rec.object_name || '" 
compile body';
               exception when others then null;
               end;
            else
               begin
                  execute immediate 'alter ' || inv_rec.object_type || ' 
"' || inv_rec.object_name || '" compile';
               exception when others then null;
               end;
            end if;
         end loop;

         anz_pref := anz;

         select count(*) into anz
         FROM user_objects
         WHERE STATUS = 'INVALID';


      end loop;

      if anz > 0 then
         declare errm varchar2(2048);
         begin
            errm := 'The following ' || anz || ' Objects compiled with 
error:';
            for inv_rec in inv loop
               if length(errm) < 1950 then
                  errm := errm || chr(10) || inv_rec.object_name || ' (' 
|| inv_rec.object_type || ')';
               else
                  errm := errm || chr(10) || '...';
                  exit;
               end if;
            end loop;
            raise_application_error(-20001, errm);
         end;
      end if;

   end recompile_invalid_objects;

which does not care about dependencies and right orders at all, but does a great job !!

Matthias Received on Mon Dec 16 2002 - 07:11:27 CET