Re: Recompile triggers

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 2 Oct 2002 12:11:46 -0700
Message-ID: <92eeeff0.0210021111.3b35d669_at_posting.google.com>

"Gawie Malherbe" <gawiemalherbe_at_removethisandchangenot.notmail.com> wrote in message news:<3d9ae4c0$0$18847_at_hades.is.co.za>...
> Hi All,
>
> I need an effective way to recompile ALL my triggers in a Oracle 8 DB. Can
> one use a script (without knowing the names of all the triggers)?
>
> G

This can compile everything for you.

SET HEADING OFF
SELECT 'There are [ ' || TO_CHAR(count(*)) ||

        ' ] invalid objects. Recompiling invalid objects...' FROM user_objects
WHERE status = 'INVALID';
SET HEADING ON DECLARE

   obj_name_    User_Objects.object_name%TYPE;
   obj_type_    User_Objects.object_type%TYPE;   
   str_run_     VARCHAR2(200);
   CURSOR Invalid_Objects_ IS
      SELECT object_name, object_type
      FROM user_objects
      WHERE status = 'INVALID'
      ORDER BY object_type ASC;        

BEGIN
   FOR Get_Rec_ IN Invalid_Objects_ LOOP

      BEGIN
         obj_name_ := Get_Rec_.object_name;
         obj_type_ := Get_Rec_.object_type;        
         IF (obj_type_ = 'FUNCTION') THEN 
            str_run_ := 'ALTER FUNCTION ' || obj_name_ || ' COMPILE';
         ELSIF (obj_type_ = 'JAVA SOURCE') THEN
            str_run_ := 'ALTER JAVA SOURCE "' || obj_name_ || '"
COMPILE';
         ELSIF (obj_type_ = 'PACKAGE') THEN
            str_run_ := 'ALTER PACKAGE ' || obj_name_ || ' COMPILE';
         ELSIF (obj_type_ = 'PACKAGE BODY') THEN 
            str_run_ := 'ALTER PACKAGE ' || obj_name_ || ' COMPILE
BODY';
         ELSIF (obj_type_ = 'PROCEDURE') THEN 
            str_run_ := 'ALTER PROCEDURE ' || obj_name_ || ' COMPILE';
         ELSIF (obj_type_ = 'TRIGGER') THEN 
            str_run_ := 'ALTER TRIGGER ' || obj_name_ || ' COMPILE';
         ELSIF (obj_type_ = 'VIEW') THEN 
            str_run_ := 'ALTER VIEW ' || obj_name_ || ' COMPILE';
         END IF;
         --
         EXECUTE IMMEDIATE str_run_;
         EXCEPTION
            WHEN OTHERS THEN
               -- Suppress errors
               BEGIN
                  NULL;
               END;
      END;                    
   END LOOP;                      

END;
/

SET HEADING OFF
SELECT 'There are [ ' || TO_CHAR(count(*)) ||

        ' ] invalid objects after recompile.' FROM user_objects
WHERE status = 'INVALID';
SET HEADING ON Received on Wed Oct 02 2002 - 21:11:46 CEST