Skip to content


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.

SQL:
  1. SELECT CASE object_type
  2.        WHEN 'PACKAGE' THEN
  3.         'ALTER '||object_type||' '||owner||'.'||object_name||' COMPILE;'
  4.        ELSE
  5.         'ALTER PACKAGE '||owner||'.'||object_name||' COMPILE BODY;'
  6.        END
  7.   FROM dba_objects
  8.  WHERE STATUS = 'INVALID'
  9.    AND object_type IN ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE');

Posted in Blog, Technology.

Tagged with , , , , .


4 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

  1. Steven Feuerstein says

    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

  2. plsql lover says

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

  3. Matt says

    @plsql lover

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

  4. gfh says

    @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?



Some HTML is OK

or, reply to this post via trackback.