Compiling Invalid Objects in PL/SQL

Jun 25, 2008

—

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');

Comments

4 responses to “Compiling Invalid Objects in PL/SQL”

Steven Feuerstein

June 26, 2008

I just thought I’d notify your readers that Solomon Yakobson, a topnotch DBA/developer, several years ago created a very powerful recompile utility that will recompile all invalid objects in dependency order. You can obtain that script from the recompile.sql file in the demo.zip download at my PL/SQL Obsession site: http://www.ToadWorld.com/SF – just click on the Trainings, Seminars, Presentations link and then click on demo.zip.

SF
plsql lover

June 27, 2008

huh? darker side? was wrong with pl/sql?
Matt

June 28, 2008

@plsql lover

I say darker side because I am a MUCH bigger fan of PHP than PL/SQL.
gfh

July 6, 2008

@plsql lover
I say darker side because I am a MUCH bigger fan of PHP than PL/SQL.

@plsql lover
I say darker side because I am a MUCH bigger fan of PHP than PL/SQL.

@plsql lover
I say darker side because I am a MUCH bigger fan of PHP than PL/SQL.

@plsql lover
I say darker side because I am a MUCH bigger fan of PHP than PL/SQL.

@plsql lover
I say darker side because I am a MUCH bigger fan of PHP than PL/SQL.

@plsql lover
I say darker side because I am a MUCH bigger fan of PHP than PL/SQL.

@plsql lover
I say darker side because I am a MUCH bigger fan of PHP than PL/SQL.

huh? darker side? was wrong with pl/sql?