Re: How to recompile all my stored procedures ?

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 24 Feb 2005 01:43:18 -0800
Message-ID: <1109238198.619509.83870@o13g2000cwo.googlegroups.com>

fmarchioni_at_libero.it wrote:
> Hi Oracle users,
> I'm again asking your opinion about pl-sql.
> Does anybody know how to recompile all stored procedures
> in one shot using a batch file ?
>
> One step back. We have all stored procedures versioned on CVS.
> Every time there's a new release of our application
> I'd like to validate all pl-sql packages found on CVS
> because it can happen that somebody forgot to "check in" changes to
> procedures.
>
> So this is the scenario. How would you validate in batch all
procedures
> ?
>
> (Ps I cannot simply import all .sql files in toad and validate them.
> I'd like rather, if possible, to integrate it with the batch file we
> use to build Java classes)
>
> Thanks
> Francesco

I posted this in another thread also. Put this in a sql script and run it as many times as you like.

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

        ' ] invalid objects. Recompiling objects, Please wait . . .' 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);
   cid_         INTEGER;
   ret_         INTEGER;
   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_ = 'JAVA CLASS') THEN
            str_run_ := 'ALTER JAVA CLASS "' || obj_name_ || '"
RESOLVE';
         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';
         ELSIF (obj_type_ = 'MATERIALIZED VIEW') THEN
            str_run_ := 'ALTER MATERIALIZED VIEW ' || obj_name_ || '
COMPILE';
         ELSIF (obj_type_ = 'DIMENSION') THEN
            str_run_ := 'ALTER DIMENSION ' || obj_name_ || ' COMPILE';
         ELSIF (obj_type_ = 'TYPE') THEN
            str_run_ := 'ALTER TYPE ' || obj_name_ || ' COMPILE';
         END IF;
         --
         cid_ := DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(cid_, str_run_, DBMS_SQL.NATIVE);
         ret_ := DBMS_SQL.EXECUTE(cid_);
         DBMS_SQL.CLOSE_CURSOR(cid_);
      EXCEPTION
         WHEN OTHERS THEN
            BEGIN
               IF (DBMS_SQL.IS_OPEN(cid_)) THEN
                  DBMS_SQL.CLOSE_CURSOR(cid_);
               END IF;
            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 Regards
/Rauf Received on Thu Feb 24 2005 - 03:43:18 CST