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

Discuss This Article


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

  1. AvatarSteven Feuerstein

    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

    Reply to this comment.
    1
  2. Avatarplsql lover

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

    Reply to this comment.
    2
  3. AvatarMatt
    Author Comment

    @plsql lover

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

    Reply to this comment.
    3
  4. Avatargfh

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

    Reply to this comment.
    4

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Comment Preview:

oracle compile trigger (21) - Compile Invalid Objects (15) - oracle compile package (13) - object (12) - invalid objects (12) - oracle compile invalid objects (11) - alter package compile (10) - compile objects (10) - plsql compile (10) - Solomon Yakobson (9) - oracle compile dependencies (8) - alter compile (7) - select invalid objects (7) - pl/sql compile package (6) - compiling invalid objects in oracle (6) - oracle query invalid objects (6) - compile plsql (6) - compile objects script (5) - compile PL/SQL (5) - Solomon Yakobson recompile script (5) - SQL COMPILE (5) - oracle compile all (5) - plsql compile package (5) - find invalid objects oracle (5) - Solomon Yakobson compile (5) - sql compile package (4) - compile invalid objects in oracle (4) - oracle compile with dependencies (4) - Recompile script from Solomon Yakobson (4) - compile invalids oracle (4) - compiling a package in oracle (4) - ORACLE SQL compile dependencies (4) - oracle sql compile view (4) - alter compile all (4) - alter package body compile (4) - ALTER PACKAGE COMPILE DEPENDENCIES (4) - how to fix oracle invalid objects, Oracle 10G (4) - oracle compile dependency (4) - Compile PL/SQL package (4) - plsql compile trigger (4) - compile invalid objects sql (4) - pl sql compile objects (4) - COMPILE OBJECT PL/SQL (4) - alter package compile dependency (4) - compile plsql package (3) - oracle plsql compile dependencies (3) - how to compile invalid objects in oracle (3) - compile object in pl/sql (3) - script to compile invalid objects (3) - compile all invalid objects (3) - recompile invalid objects (3) - FIND INVALID TRIGGERS (3) - compile sql (3) - COMPILE OBJECT SQL (3) - COMPILE DEPENDENCIES PL/SQL (3) - invalid package body (3) - query to find invalid objects in oracle (3) - compile invalid views (3) - sql compile objects (3) - how to query invalid objects (3) - compile view sql (3) - pl sql compile view (3) - compile oracle sql (3) - pl sql and recompile all the package (3) - how to compile invalids (3) - oracle compile view (3) - oracle compile all invalid objects (3) - pl/sql compile invalid (3) - oracle sql developer compile invalid objects (3) - Pl/SQL compile view (3) - compile all objects (3) - oracle package invalid (3) - compile invalid object (3) - compiling invalids (3) - COMPILE DEPENDENCIES IN ORACLE (3) - SELECTING invalid procedures oracle (3) - ALTER COMPILE PACKAGE (3) - compile invalid triggers (3) - packages triggers plsql (3) - ORACLE RECOMPILE dependencies (3) - recompile.sql (3) - compile object alter (3) - oracle compile packages (3) - PL/SQL recompile trigger (3) - how to compile invalid object (3) - recompile invalid objects oracle sql (3) - compile pl sql (3) - PL/SQl Script to compile the invalid views (3) - oracle compile objects (3) - compile invalid objects oracle (3) - sql find invalid objects (3) - Solomon Yakobson recompile (3) - plsql compile procedure (3) - compile package sql (3) - pl sql compile trigger (3) - oracle compile objects dependency (3) - compiling DB objects (3) - oracle compile invalid object (3) - plsql compile invalid objects (3) - compile db objects (3) - compile all invalid object (3) - compile invalid objects script (3) - compiling triggers (3) - compile view PL/SQL (3) - compile view oracle (3) - ORacle recompiling invalid packages (3) - alter package compile database link (3) - pl sql compile invalid (3) - oracle package compiling (3) - compile invalid package (2) - COMPILE OBJECTS SQL (2) - pl/sql invalid package (2) - compile invalid in oracle (2) - compiling objects with dependencies (2) - compile pl/sql packages (2) - como auto compilar package no oracle 9 (2) - recompile.sql oracle (2) - PL SQL compile invalid objects (2) - Compile Invalid Objects PL/SQL Developer (2) - recompile Solomon Yakobson (2) - compile a package sql (2) - compile object in sql (2) - pl/sql developer compile invalid objects (2) - compile all pl/sql (2) - compile package query (2) - oracle plsql compile all objects (2) - invalid plsql package (2) - sql to compile invalid objects (2) - pl/sql Compile for invalid objects (2) - oracle/compile (2) - compile invalid (2) - compile object (2) - compile invalid Oracle objects (2) - compile dB objects with a script (2) - oracle sql compile all (2) - compile dependencies plsql (2) - query invalid packages (2) - pl/sql release compile all (2) - compile invalid object oracle (2) - pl/sql compile procedure (2) - pl/sql compile dependencies (2) - oracle auto compile package (2) - compile invalid package body (2) - find invalid objects (2) - oracle compile invalid dependency (2) - compile invalid packages in db (2) - script to compile package body (2) - How find invalid objects in Oracle (2) - invalid objects alter owner. (2) - query to compile all database object using oracle sql developer (2) -