Re: Help. NT script required to recompile Oracle Objs, urgently !.

From: Pedro Talavera <ptalavera_at_copreci.es>
Date: Wed, 27 Oct 1999 12:36:57 +0200
Message-ID: <3816D5C8.C9CD3CE9@copreci.es>

Hello:
You can generate this stored procedure that recompiles all objects and call it from a Job of
Oracle as frecuently as you need.

create or replace procedure recompilar as   OCUPADO exception;
  pragma exception_init(OCUPADO,-00054);   cadena varchar2(200);
  a integer;
  b integer;
  cursor p is
    select owner,object_name, object_type from dba_objects     where status='INVALID' and
   object_type in ('PROCEDURE','FUNCTION','VIEW','PACKAGE BODY','PACKAGE','TRIGGER')
    order by 1,2;
begin
 for r in p loop

    begin

     cadena:=r.object_type||' '||r.owner||'.'||r.object_name;
     a:=sys.dbms_sql.open_cursor;
     sys.dbms_sql.parse(a,'alter '||cadena||' compile',1);
     b:=sys.dbms_sql.execute(a);
     sys.dbms_sql.close_cursor(a);
    EXCEPTION
       WHEN OCUPADO THEN
         null;
       WHEN OTHERS THEN
         null;

    end;
 end loop;
end; Received on Wed Oct 27 1999 - 05:36:57 CDT