Re: table partition add makes package invalid

"Howard J. Rogers" wrote:

> Any DDL, of any sort, on any object, of any sort, renders all packages and
> procedures that reference that object invalid. It's been that way for ages.
> There's nothing you can do to prevent it, either. It's a design feature (we
> can't know what it is that you've done to the object, so naturally all code
> referencing it has to be invalidated).
>
> You'll find that all procedures and packages which *can* be re-validated
> will automatically do so the first time they are called after the
> invalidation (the second time if it's a remote dependency). Or you can get
> in there beforehand and manually recompile them all.
>
> Naturally, if you've just dropped a column from a table, a package that
> references that now-absent column *can't* recompile -but that's the point of
> invalidating it in the first place.
>
> But short of never doing DDL, you can't avoid invalidations.
>
> Regards
> HJR
>
> <ecubed_at_campuscwix.net> wrote in message
> news:3D2F4101.99AAAC50_at_campuscwix.net...
> > Running 8.1.6 on solaris 2.7. When I add a table partition,
> > it makes a package invalid (package uses JDBC to add new
> > records - insert rate is steady stream). Can anyone point me
> > to where I need to start checking as to why status of
> > package goes invalid?
> >
> > tks
> >

There are two pieces of DDL that I know of that do not invalidate procedures.

1. TRUNCATE TABLE t;
2. ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (field_name);

Check constraints, etc. most certainly do.

Daniel Morgan Received on Fri Jul 12 2002 - 16:16:17 CDT