Re: invalid packages

From: Brad Worsfold <bworsfold_at_vic.lgs.ca>
Date: 1997/07/22
Message-ID: <01bc96f5$a40d9a00$0faf22cf@cerulean>#1/1

rem

---

rem File Name : recompile.sql
rem Created By: Brad Worsfold, LGS Group Inc.
rem Created   : February, 1997

rem
rem Purpose : This script is designed to look at the invalid objects for the

rem		current user and then create a compilation script.  Note: This script
rem		may need to be run a few times depending on dependencies.

rem Revisions :
rem

---

set term off
set feedback off
spool c:\tmp.lst
select 'alter ' || decode(object_type, 'PACKAGE BODY', 'PACKAGE '|| object_name || ' compile body;', object_type || ' ' || object_name || ' compile;')
from user_objects
where status = 'INVALID';
spool off

@c:\tmp.lst

set term on
set feedback on

select object_name, object_type
from user_objects
where status = 'INVALID'
/

Tomm Carr <tommcatt_at_geocities.com> wrote in article <33D4E36C.204B_at_geocities.com>...
> Stefan Keller wrote:
> >
> > if I make changes in the structure of a table, and PL/SQL-Function
> > reads this table, the function gets the status ‘invalid’.
> > Does a package contain this function, the whole package is ‘invalid’.
> > In this case other functions in the same package are invalid.
> > What can I do or do I have to recompile the packages always when I
> > make changes on one of those tables?
>
> It is not difficult to create a script that searches the data dictionary
> for all stored procedures and packages that are marked INVALID and then
> performs an ALTER...RECOMPILE on them. You can then run this any time
> you do any DDL.
>
> Now that I mention it, with all the packages we have around here, I'm
> surprised we don't have one already. Well, not really -- our DB is
> pretty old and static. However, it still sounds like a good utility to
> have around.
>
> I'll play around with it and see what I can come up with. Or if someone
> already has one (or knows why my tentative idea wouldn't work or has a
> better idea), please let me know.
>
> --
> Tomm Carr
> --
> "Can you describe your assailant?"
> "No problem, Officer. That's exactly what I was doing when he hit me!"
>
Received on Tue Jul 22 1997 - 00:00:00 CDT