Larry Steinle

Whenever an Oracle view, function, procedure or package header is modified the object is invalidated and any objects that call the invalidated object become invalid. To avoid errors these objects need to be compiled. The purpose of this article is to demonstrate how to identify the invalid objects compiling them in the correct order so that all objects can be validated in one pass while providing the ability to track which objects fail to validate.

The following tree demonstrates how one Oracle object calls another Oracle object creating relationship dependencies between the objects.

World.func
–> Travel.proc
–> –> MeetPeople.proc
OtherFunk.func

If MeetPeople.proc becomes invalidated then so does Travel.proc and World.func. In order to compile these objects they must be compiled from the child leaf node up to the root node.

What’s Invalid?

We can query SYS.DBA_Objects (or SYS.All_Objects depending on your permissions) to identify which objects are no longer valid:

SELECT Object_Id, Owner, Object_Type, Object_Name, Status
  FROM SYS.DBA_Objects
 WHERE DBA_Objects.Owner NOT IN ('SYS', 'SYSTEM')
   AND UPPER(DBA_Objects.Object_Type) IN ('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TYPE', 'TYPE BODY', 'VIEW')
   AND UPPER(DBA_Objects.Status) <> 'VALID';

While this returns the list of objects that need to be recompiled we still don’t know what order to compile. If we attempt to compile World.func before the child objects have been compiled it will fail. We have to compile in reverse call order.

Object Dependencies

Any object that calls another object to perform a given task is said to be dependent upon the other object. Oracle tracks these relationships in the SYS.Public_Dependency table. Between the tables SYS.Public_Dependency and SYS.DBA_Objects we are able to reconstruct the correct call order to compile the objects.

The following query demonstrates how to get the complete order call. This query also manages recursive calls where object a calls object b which calls object a creating an unending relationship loop.

SELECT Owner,
       Object_Type,
       Object_Name,
       1 AS Call_Level
  FROM SYS.DBA_Objects
 WHERE UPPER(DBA_Objects.Status) <> 'VALID'
 UNION
SELECT Call_Chain.Dependent_Owner,
       Call_Chain.Dependent_Type,
       Call_Chain.Dependent_Name,
       Call_Chain.Call_Level
  FROM (  --Construct call chain
          SELECT Relationships.Dependent_Id,
                 Relationships.Dependent_Owner,
                 Relationships.Dependent_Name,
                 Relationships.Dependent_Type,
                 Relationships.Dependent_Status,
                 MAX(LEVEL) AS Call_Level
            FROM (  --Get object parent-child relationships
                    SELECT DBA_Objects.Object_Id,
                           DBA_Objects.Owner,
                           DBA_Objects.Object_Type,
                           DBA_Objects.Object_Name,
                           DBA_Objects.Status,
                           Dependency.Object_Id AS Dependent_Id,
                           Dependency.Owner AS Dependent_Owner,
                           Dependency.Object_Type AS Dependent_Type,
                           Dependency.Object_Name AS Dependent_Name,
                           Dependency.Status AS Dependent_Status
                      FROM (SELECT Object_Id, Owner, Object_Type, Object_Name, Status FROM SYS.DBA_Objects WHERE DBA_Objects.Owner NOT IN ('SYS', 'SYSTEM') AND UPPER(DBA_Objects.Object_Type) IN ('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TYPE', 'TYPE BODY', 'VIEW')) DBA_Objects
                      JOIN SYS.Public_Dependency
                        ON Public_Dependency.Object_Id = DBA_Objects.Object_Id AND DBA_Objects.Status <> 'VALID'
                      JOIN (SELECT Object_Id, Owner, Object_Type, Object_Name, Status FROM SYS.DBA_Objects WHERE DBA_Objects.Owner NOT IN ('SYS', 'SYSTEM') AND UPPER(DBA_Objects.Object_Type) IN ('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TYPE', 'TYPE BODY', 'VIEW')) Dependency_Key
                        ON Dependency_Key.Object_Id = Public_Dependency.Referenced_Object_Id
                      JOIN (SELECT Object_Id, Owner, Object_Type, Object_Name, Status FROM SYS.DBA_Objects WHERE DBA_Objects.Owner NOT IN ('SYS', 'SYSTEM') AND UPPER(DBA_Objects.Object_Type) IN ('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TYPE', 'TYPE BODY', 'VIEW')) Dependency
                        ON UPPER(Dependency.Object_Name) = UPPER(Dependency_Key.Object_Name)
                       AND Dependency_Key.Object_Id <> DBA_Objects.Object_Id
                 ) Relationships
          CONNECT BY PRIOR Relationships.Object_Id = Relationships.Dependent_Id
                 AND Relationships.Dependent_Id > PRIOR Relationships.Dependent_Id
          GROUP BY Relationships.Dependent_Id,
                 Relationships.Dependent_Owner,
                 Relationships.Dependent_Name,
                 Relationships.Dependent_Type,
                 Relationships.Dependent_Status
       ) Call_Chain
 WHERE UPPER(Call_Chain.Dependent_Status) <> 'VALID'
 ORDER BY Call_Level DESC, Object_Name ASC;

Compiling the Invalid Objects

At this point we can iterate thru each record calling the appropriate SQL command to compile the object:

BEGIN
  FOR Invalid_Object_Rec IN (
    SELECT Owner,
           Object_Type,
           Object_Name,
           1 AS Call_Level
      FROM SYS.DBA_Objects
     WHERE UPPER(DBA_Objects.Status) <> 'VALID'
     UNION
    SELECT Call_Chain.Dependent_Owner,
           Call_Chain.Dependent_Type,
           Call_Chain.Dependent_Name,
           Call_Chain.Call_Level
      FROM (  --Construct call chain
              SELECT Relationships.Dependent_Id,
                     Relationships.Dependent_Owner,
                     Relationships.Dependent_Name,
                     Relationships.Dependent_Type,
                     Relationships.Dependent_Status,
                     MAX(LEVEL) AS Call_Level
                FROM (  --Get object parent-child relationships
                        SELECT DBA_Objects.Object_Id,
                               DBA_Objects.Owner,
                               DBA_Objects.Object_Type,
                               DBA_Objects.Object_Name,
                               DBA_Objects.Status,
                               Dependency.Object_Id AS Dependent_Id,
                               Dependency.Owner AS Dependent_Owner,
                               Dependency.Object_Type AS Dependent_Type,
                               Dependency.Object_Name AS Dependent_Name,
                               Dependency.Status AS Dependent_Status
                          FROM (
                                 SELECT Object_Id,
                                        Owner,
                                        Object_Type,
                                        Object_Name,
                                        Status
                                   FROM SYS.DBA_Objects
                                  WHERE DBA_Objects.Owner NOT IN ('SYS', 'SYSTEM')
                                    AND UPPER(DBA_Objects.Object_Type) IN ('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TYPE', 'TYPE BODY', 'VIEW')
                               ) DBA_Objects
                          JOIN SYS.Public_Dependency
                            ON Public_Dependency.Object_Id = DBA_Objects.Object_Id
                           AND DBA_Objects.Status <> 'VALID'
                          JOIN (
                                 SELECT Object_Id,
                                        Owner,
                                        Object_Type,
                                        Object_Name,
                                        Status
                                   FROM SYS.DBA_Objects
                                  WHERE DBA_Objects.Owner NOT IN ('SYS', 'SYSTEM')
                                    AND UPPER(DBA_Objects.Object_Type) IN ('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TYPE', 'TYPE BODY', 'VIEW')
                               ) Dependency_Key
                            ON Dependency_Key.Object_Id = Public_Dependency.Referenced_Object_Id
                          JOIN (
                                 SELECT Object_Id,
                                        Owner,
                                        Object_Type,
                                        Object_Name,
                                        Status
                                   FROM SYS.DBA_Objects
                                  WHERE DBA_Objects.Owner NOT IN ('SYS', 'SYSTEM')
                                    AND UPPER(DBA_Objects.Object_Type) IN ('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TYPE', 'TYPE BODY', 'VIEW')
                               ) Dependency
                            ON UPPER(Dependency.Object_Name) = UPPER(Dependency_Key.Object_Name)
                           AND Dependency_Key.Object_Id <> DBA_Objects.Object_Id
                     ) Relationships
            CONNECT BY PRIOR Relationships.Object_Id = Relationships.Dependent_Id
                   AND Relationships.Dependent_Id > PRIOR Relationships.Dependent_Id
              GROUP BY Relationships.Dependent_Id,
                       Relationships.Dependent_Owner,
                       Relationships.Dependent_Name,
                       Relationships.Dependent_Type,
                       Relationships.Dependent_Status
           ) Call_Chain
       WHERE UPPER(Call_Chain.Dependent_Status) <> 'VALID'
    ORDER BY Call_Level DESC, Object_Name ASC
  ) LOOP
    BEGIN
      IF UPPER(Invalid_Object_Rec.Object_Type) IN ('TYPE BODY', 'PACKAGE BODY') THEN
        EXECUTE IMMEDIATE 'ALTER ' || Invalid_Object_Rec.Object_Type || ' "' || Invalid_Object_Rec.Owner || '"."' || Invalid_Object_Rec.Name || '" COMPILE BODY';
      ELSE
        EXECUTE IMMEDIATE 'ALTER ' || Invalid_Object_Rec.Object_Type || ' "' || Invalid_Object_Rec.Owner || '"."' || Invalid_Object_Rec.Name || '" COMPILE';
      END IF;
    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE("Failed to compile " || Invalid_Object_Rec.Owner || '.' || Invalid_Object_Rec.Object_Name || ' (' || Invalid_Object_Rec.Object_Type || ')';
    END;
  END LOOP;
END;

Summary

In this article we saw how to build an Oracle Compiler using Oracle SQL code. This could just as easily have been done from a Java or VS.Net application. Regardless, with a single pass we have a validated Oracle database.