Compiling Invalid Objects in PL/SQL

While I do a lot of and prefer PHP and JavaScript development, my daily job has a darker side…PL/SQL. At times the packages that I oversee invalidate during upgrades or poor compilations of a package that ends up having a ripple effect.

Here’s a query I wrote to find the invalid objects and generate compile statements to attempt to fix whatever is busted.

SELECT CASE object_type
       WHEN 'PACKAGE' THEN
        'ALTER '||object_type||' '||owner||'.'||object_name||' COMPILE;'
       ELSE
        'ALTER PACKAGE '||owner||'.'||object_name||' COMPILE BODY;'
       END
  FROM dba_objects
 WHERE status = 'INVALID'
   AND object_type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE');