Re: Need recompile invalid objects script...

From: David Pattinson <david_at_addease.com.au>
Date: Fri, 04 Jun 1999 10:28:12 +1000
Message-ID: <37571D9C.B4153D9D@addease.com.au>

Here's a couple of scripts I wrote to achieve this. They haven't been 'tidied up', but they work well for me. You run the recompileManager procedure passing in your schema owner, it runs the recompileObjects proc until it fails to recompile any further objects (this is to get around object dependancies).

------------SCRIPT STARTS
CREATE OR REPLACE PROCEDURE sp_admin_recompileObjects(pSchemaOwner IN VARCHAR2)

IS

v_cursorID INTEGER ;
v_statement VARCHAR2(400) ;
v_action  VARCHAR(200) ;

--GET THE LIST OF PACKAGES, PROCEDURES AND FUNCTIONS OWNED BY "pSchemaOwner"
cursor constr_cursor is
 select *
 from all_objects
 where owner = pSchemaOwner
 and object_type in ('VIEW', 'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
 and object_name not like 'SP_ADMIN_%'
 and status != 'VALID'
 order by object_type
;

constr_val constr_cursor%rowtype;

begin
 open constr_cursor ;
 --LOOP THROUGH THE OBJECTS ONE BY ONE
 loop
  begin
  fetch constr_cursor into constr_val ;   exit when constr_cursor%NOTFOUND;
  v_cursorID := DBMS_SQL.OPEN_CURSOR;

  --BUILD THE COMMAND
  begin
   select DECODE(constr_val.object_type, 'PROCEDURE', 'ALTER PROCEDURE ',

      'FUNCTION', 'ALTER FUNCTION ',
      'VIEW', 'ALTER VIEW ',
      'ALTER PACKAGE ')

   into v_action from DUAL;
   v_statement := v_action||constr_val.object_name||' COMPILE';    --PARSE THE COMMAND, WHICH ALSO EXECUTES IT AS IT IS DDL    DBMS_SQL.PARSE(v_cursorID, v_statement, DBMS_SQL.NATIVE);    DBMS_SQL.CLOSE_CURSOR(v_cursorID);
  exception
   when others then
    --IGNORE SUCCESS WITH COMPILATION ERROR MESSAGE     if SQLCODE != -24344 then
     RAISE;
    end if;
  end;
 EXCEPTION
   WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(v_cursorID);
      RAISE;
 end;
 end loop ;
 close constr_cursor ;
EXCEPTION
 WHEN OTHERS THEN
  close constr_cursor ;
  RAISE;
end sp_admin_recompileObjects;
/

CREATE OR REPLACE PROCEDURE SP_ADMIN_RECOMPILEMANAGER(pSchemaOwner IN VARCHAR2)

IS

--WRAPPER PROCEDURE TO CALL SP_ADMIN_RECOMPILEOBJECTS UNTIL NO MORE OBJECTS CAN

--BE MADE VALID. IT COMPARES THE VALID COUNT AFTER PROCESSING WITH THE INITIAL
--VALID COUNT AND STOPS WHEN THEY ARE THE SAME.
v_preNonValidCount INTEGER ;
v_postNonValidCount INTEGER ;

begin

--INITIALISE COUNT VARIABLES
 select count(*) into v_preNonValidCount  from all_objects
 where owner = pSchemaOwner
 and object_type in ('VIEW', 'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
 and object_name not like 'SP_ADMIN_%'
 and status != 'VALID'
 ;

 if v_preNonValidCount > 0 then
 loop
  begin
   exit when v_preNonValidCount = v_postNonValidCount ;    select count(*) into v_preNonValidCount    from all_objects
   where owner = pSchemaOwner
   and object_type in ('VIEW', 'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
   and object_name not like 'SP_ADMIN_%'    and status != 'VALID'
   ;

   SP_ADMIN_RECOMPILEOBJECTS(pSchemaOwner) ;

   select count(*) into v_postNonValidCount    from all_objects
   where owner = pSchemaOwner
   and object_type in ('VIEW', 'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
   and object_name not like 'SP_ADMIN_%'    and status != 'VALID'
   ;
  end ;
 end loop;
 end if;

end sp_admin_recompilemanager ;
/

--------------------END OF SCRIPT
Mark Gumbs wrote:

> 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 - 19:28:12 CDT