Program for recompiling invalid packages

When we compile a package specification and if it has errors, all the dependent packages will become invalid.
In such cases we may need a script which automatically identifies the packages which are in invalid status and recompile them to bring them back to valid status. This post provides that script.

The following script
1. Identifies all the packages which are in invalid status.
3. fetches them in order of their dependency.
3. Recompiles them.
4. Connect by NOCYCLE clause ensures that the package interdepencies are also taken care.

Script :

DECLARE
   l_object_owner   VARCHAR2 (4000);
   l_object_name    VARCHAR2 (4000);
   l_object_type    VARCHAR2 (4000);

   CURSOR object_details (c_object_id NUMBER)
   IS
      SELECT owner, object_name, object_type
        FROM dba_objects
       WHERE object_id = c_object_id;
BEGIN
   FOR cur_rec IN
      (SELECT     MAX (LEVEL) dlevel, object_id
             FROM SYS.public_dependency
       START WITH object_id IN (
                     SELECT object_id
                       FROM dba_objects
                      WHERE object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)
                        AND status != ‘VALID’
                        AND object_name LIKE ‘XX%’
                     MINUS
                     SELECT purge_object
                       FROM recyclebin)
       CONNECT BY NOCYCLE object_id = PRIOR referenced_object_id
         GROUP BY object_id
           HAVING MIN (LEVEL) = 1
       UNION ALL
       SELECT   1 dlevel, object_id
           FROM dba_objects o
          WHERE object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)
            AND status != ‘VALID’
            AND object_name LIKE ‘XX%’
            AND NOT EXISTS (SELECT 1
                              FROM SYS.public_dependency d
                             WHERE d.object_id = o.object_id)
       ORDER BY 1 DESC)
   LOOP
      BEGIN
         OPEN object_details (cur_rec.object_id);

         FETCH object_details
          INTO l_object_owner, l_object_name, l_object_type;

         CLOSE object_details;

         IF l_object_type = ‘PACKAGE’
         THEN
            EXECUTE IMMEDIATE    ‘ALTER ‘
                              || l_object_type
                              || ‘ “‘
                              || l_object_owner
                              || ‘”.”‘
                              || l_object_name
                              || ‘” COMPILE’;
         ELSE
            EXECUTE IMMEDIATE    ‘ALTER PACKAGE “‘
                              || l_object_owner
                              || ‘”.”‘
                              || l_object_name
                              || ‘” COMPILE BODY’;
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
   END LOOP;
END;
/

Advertisements
This entry was posted in PLSQL. Bookmark the permalink.

2 Responses to Program for recompiling invalid packages

  1. Very useful for me. I recently wrote an article (in Czech) on the same topic, which includes my own version of the script 🙂 What a coincidence 🙂

    http://since75.cz/2010/08/oracle-db-jak-rekurzivne-rekompilovat-invalidni-objekty/

    Thank you.

  2. Pingback: How to recompile invalid objects recursively | Martin Mareš : blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s