Re: Alter any view?

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 15 Feb 2005 15:59:27 -0800
Message-ID: <1108511967.487305.119030@g14g2000cwa.googlegroups.com>

ford_desperado_at_yahoo.com wrote:
> I would do anything to avoid this execute immediate stuff.
> I work hard to reduce parse/compile count, replace dynamic SQL with
> bind variables and so on.
> What you are suggesting would definitely slow down the server

I have been using this script for a long time. You can convert this into a procedure (User_Objects to All_Objects etc) as suggested by David and further emphasised by Dave. If you want to run DDL from PL/SQL, then you have to use Dynamic Sql... there is no other way around it.

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 Tue Feb 15 2005 - 17:59:27 CST