CREATE OR REPLACE FUNCTION SYSRECOMP
/*************************************************************
*************************************************************
*************************************************************
*************************************************************

A More Sophisticated Recompile Utility
Written BY Solomon Yakobson, syakobson@erols.com

The code FOR this utility IS also available IN the syrecomp.sp FILE.

Browsing through Useful PL/SQL Code published IN 1/26 - NEW Code ARCHIVE
I noticed the Program Recompilation utility. Program description tells us
that "this procedure will sweep through the ALL_OBJECTS catalogue for any
INVALID stored programs and use the DBMS_DDL.ALTER_COMPILE built-in to
recompile that program. It makes sure that package specifications are
compiled before their package bodies -- and it only recompiles specs when
explicitly needed". 

There are several issues, IN my opinion, related TO the code.

1. 	Data DICTIONARY VIEW ALL_OBJECTS LISTS objects accessible TO the USER.
This means Recompilation utility can ONLY Recompile objects accessible TO
stored program owner (assuming that owner has ALTER ANY PROCEDURE privilege).
IF you DO NOT realize that AND CREATE Recompilation utility UNDER USER who
does NOT see ALL stored procedures/functions/packages USING % could be misleading.
IF you issue EXEC Recompile TO Recompile ALL your invalid stored
procedures/functions/packages, but Recompilation utility owner does NOT see
SOME OF them via HIS all_objects you will get PL/SQL PROCEDURE successfully
completed WHILE NOT ALL the objects are recompiled. I think ALL_OBJECTS should
be replaced BY DBA_OBJECTS.

2. 	Although USING DBMS_DDL IS more convenient THAN DBMS_SQL, it does NOT
allow recompiling TRIGGERS AND VIEWS. USING DBMS_SQL would widen
Recompilation utility usability.

3. 	Now most important. Recompilation utility DOES NOT CONSIDER OBJECT
DEPENDENCY (EXCEPT PACKAGE BODY TO its OWN PACKAGE specification). Assume
PACKAGE PKG1 depends ON a FUNCTION F1. Assume F1 AND therefore PKG1 are invalid.
THEN EXEC Recompile will Recompile PKG1 first AND F1 second. AS a result
PKG1 status will remain invalid. This means you possibly need TO RUN
Recompilation utility multiple times.

Below IS my Recompile utility I am USING quite often. Patching AND upgrading
Oracle Financials leaves behind invalid objects once IN a WHILE. This
utility will Recompile your request IN right dependency ORDER. It also will
tell you what IS object status AFTER recompilation. IF object TYPE IS NOT
recompilable you will see a message AND a corresponding bit IN FUNCTION
RETURN value IS SET TO one. Same thing happens WHEN you are requesting TO
Recompile an object REFERENCING an invalid object outside OF your request
(therefore recompiling your object will NOT make it valid). IN both cases
utility will proceed TO NEXT object: 

*************************************************************
*************************************************************
**************************************************************/

 (
   o_owner                    IN       VARCHAR2 := USER
  ,o_name                     IN       VARCHAR2 := '%'
  ,o_type                     IN       VARCHAR2 := '%'
  ,o_status                   IN       VARCHAR2 := 'INVALID'
)
   RETURN NUMBER IS
   -- Return Codes

   invalid_type CONSTANT INTEGER := 1;
   invalid_parent CONSTANT INTEGER := 2;
   cnt NUMBER;
   dyncur INTEGER;
   type_status INTEGER := 0;
   parent_status INTEGER := 0;
   object_status VARCHAR2 (30);

   CURSOR invalid_parent_cursor (
      oowner                              VARCHAR2
     ,oname                               VARCHAR2
     ,otype                               VARCHAR2
     ,ostatus                             VARCHAR2
     ,OID                                 NUMBER
   ) IS
      SELECT /*+ RULE */
             o.object_id
        FROM public_dependency d
            ,dba_objects o
       WHERE d.object_id = OID
         AND o.object_id = d.referenced_object_id
         AND o.status != 'VALID'
      MINUS
      SELECT /*+ RULE */
             object_id
        FROM dba_objects
       WHERE owner LIKE UPPER (oowner)
         AND object_name LIKE UPPER (oname)
         AND object_type LIKE UPPER (otype)
         AND status LIKE UPPER (ostatus);

   CURSOR recompile_cursor (
      OID                                 NUMBER
   ) IS
      SELECT /*+ RULE */
             'ALTER '
             || DECODE (object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' '
             || owner || '.' || object_name || ' COMPILE '
             || DECODE (object_type, 'PACKAGE BODY', ' BODY', '') stmt
            ,object_type
            ,owner
            ,object_name
        FROM dba_objects
       WHERE object_id = OID;

   recompile_record recompile_cursor%ROWTYPE;

   CURSOR obj_cursor (
      oowner                              VARCHAR2
     ,oname                               VARCHAR2
     ,otype                               VARCHAR2
     ,ostatus                             VARCHAR2
   ) IS
      SELECT     /*+ RULE */
                 MAX (LEVEL) dlevel
                ,object_id
            FROM sys.public_dependency
      START WITH object_id IN (SELECT object_id
                                 FROM dba_objects
                                WHERE owner LIKE UPPER (oowner)
                                  AND object_name LIKE UPPER (oname)
                                  AND object_type LIKE UPPER (otype)
                                  AND status LIKE UPPER (ostatus))
      CONNECT BY object_id = PRIOR referenced_object_id
        GROUP BY object_id
          HAVING MIN (LEVEL) = 1
        ORDER BY dlevel DESC;

   CURSOR status_cursor (
      OID                                 NUMBER
   ) IS
      SELECT /*+ RULE */
             status
        FROM dba_objects
       WHERE object_id = OID;
BEGIN
   -- Recompile requested objects based on their dependency levels.

   DBMS_OUTPUT.put_line (CHR (0));
   DBMS_OUTPUT.put_line ('                            RECOMPILING OBJECTS');
   DBMS_OUTPUT.put_line (CHR (0));
   DBMS_OUTPUT.put_line (
      '                            Object Owner is  ' || o_owner
   );
   DBMS_OUTPUT.put_line (
      '                            Object Name is   ' || o_name
   );
   DBMS_OUTPUT.put_line (
      '                            Object Type is   ' || o_type
   );
   DBMS_OUTPUT.put_line (
      '                            Object Status is ' || o_status
   );
   DBMS_OUTPUT.put_line (CHR (0));
   dyncur := DBMS_SQL.open_cursor;

   FOR obj_record IN obj_cursor (o_owner, o_name, o_type, o_status) LOOP
      OPEN recompile_cursor (obj_record.object_id);
      FETCH recompile_cursor INTO recompile_record;
      CLOSE recompile_cursor;

      -- We can recompile only Functions, Packages, Package Bodies,
      -- Procedures, Triggers and Views.

      IF recompile_record.object_type IN
            ('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TRIGGER', 'VIEW') THEN
         -- There is no sense to recompile an object that depends on
         -- invalid objects outside of the current recompile request.

         OPEN invalid_parent_cursor (
            o_owner
           ,o_name
           ,o_type
           ,o_status
           ,obj_record.object_id
         );
         FETCH invalid_parent_cursor INTO cnt;

         IF invalid_parent_cursor%NOTFOUND THEN
            -- Recompile object.

            DBMS_SQL.parse (dyncur, recompile_record.stmt, DBMS_SQL.native);
            cnt := DBMS_SQL.EXECUTE (dyncur);
            OPEN status_cursor (obj_record.object_id);
            FETCH status_cursor INTO object_status;
            CLOSE status_cursor;
            DBMS_OUTPUT.put_line (
               recompile_record.object_type || ' ' || recompile_record.owner || '.'
               || recompile_record.object_name
               || ' is recompiled. Object status is ' || object_status || '.'
            );
         ELSE
            DBMS_OUTPUT.put_line (
               recompile_record.object_type || ' ' || recompile_record.owner || '.'
               || recompile_record.object_name || ' references invalid object(s)'
               || ' outside of this request.'
            );
            parent_status := invalid_parent;
         END IF;

         CLOSE invalid_parent_cursor;
      ELSE
         DBMS_OUTPUT.put_line (
            recompile_record.owner || '.' || recompile_record.object_name || ' is a '
            || recompile_record.object_type || ' and can not be recompiled.'
         );
         type_status := invalid_type;
      END IF;
   END LOOP;

   DBMS_SQL.close_cursor (dyncur);
   RETURN type_status + parent_status;
EXCEPTION
   WHEN OTHERS THEN
      IF obj_cursor%ISOPEN THEN
         CLOSE obj_cursor;
      END IF;

      IF recompile_cursor%ISOPEN THEN
         CLOSE recompile_cursor;
      END IF;

      IF invalid_parent_cursor%ISOPEN THEN
         CLOSE invalid_parent_cursor;
      END IF;

      IF status_cursor%ISOPEN THEN
         CLOSE status_cursor;
      END IF;

      IF DBMS_SQL.is_open (dyncur) THEN
         DBMS_SQL.close_cursor (dyncur);
      END IF;

      RAISE;
END;
/