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:
-
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');
Discuss This Article
|
|
4 Responses to “Compiling Invalid Objects in PL/SQL”
-
Steven Feuerstein
Posted: Jun 26th, 2008 at 6:54 am1 -
plsql lover
Posted: Jun 27th, 2008 at 9:47 pmReply to this comment.huh? darker side? was wrong with pl/sql?
2 -
Matt
Posted: Jun 28th, 2008 at 3:56 amReply to this comment.@plsql lover
I say darker side because I am a MUCH bigger fan of PHP than PL/SQL.
3 -
gfh
Posted: Jul 6th, 2008 at 12:55 amReply to this comment.@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?
4
Leave a Reply
Comment Preview:
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