Re: Need recompile invalid objects script...

From: Mark Gumbs <mgumbs_at_nospam.hotmail.com>
Date: Thu, 3 Jun 1999 17:51:51 +0100
Message-ID: <3756b07b.0@145.227.194.253>

I found this on this newsgroup, may be useful 4 u.

Mark

---

CREATE OR REPLACE PROCEDURE recompile

   (status_in IN VARCHAR2 := 'INVALID',
    name_in IN VARCHAR2 := '%',
    type_in IN VARCHAR2 := '%',

    schema_in IN VARCHAR2 := USER)
IS

   v_objtype VARCHAR2(100);

   CURSOR obj_cur IS

      SELECT owner, object_name, object_type
        FROM ALL_OBJECTS
       WHERE status LIKE UPPER (status_in)
         AND object_name LIKE UPPER (name_in)
         AND object_type LIKE UPPER (type_in)
         AND owner LIKE UPPER (schema_in)
      ORDER BY
         DECODE (object_type,
            'PACKAGE', 1, 'PACKAGE BODY', 2,
            'FUNCTION', 3, 'PROCEDURE', 4);

BEGIN
   FOR rec IN obj_cur
   LOOP

      IF rec.object_type = 'PACKAGE'
      THEN
         v_objtype := 'PACKAGE SPECIFICATION';
      ELSE
         v_objtype := rec.object_type;
      END IF;

      DBMS_DDL.ALTER_COMPILE (v_objtype, rec.owner, rec.object_name);

      DBMS_OUTPUT.PUT_LINE
         ('Compiled ' || v_objtype || ' of ' ||
          rec.owner || '.' || rec.object_name);

   END LOOP;
END;
/

---

tim_mcconechy_at_my-deja.com wrote in message <7j5ki8$c3k$1_at_nnrp1.deja.com>...
>How do I build a recompile invalid objects script???
>
>TIA
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Thu Jun 03 1999 - 11:51:51 CDT