Re: Package becomes INVALID when a partiiton is dropped

Hello Deepak,

First off I need to correct myself. Oracle correctly invalidates your package. I wrongly assumed that a plsql object should only depend on the signature of a table and not on its partitions. In case of adding a partition this is true and a plsql object cannot be dependent on something that is not yet there. However dropping a partition is another matter because it is possible to reference a partition of a table in SQL directly thereby creating a direct dependency.

I don't think it is really needed to add an extra column to your control table when the information is already available in the DD. So, why not use that directly?

Hth & regards
Eric Valk

2005/6/28, Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>:
> Tom,
>
> First of all, there are 2 packages - pkg_A and pkg_B.
>
> Package pkg_A creates pkg_B. In our context it is
> pkg_B that gets invalid.
>
> To generate text for pkg_B, the pkg_A builds statement
> like the following "dynamically" -
>
> TYPE typAppLevelRec IS RECORD (
> COL1 t1.COL1%TYPE,
> COL2 t1.COL2%TYPE,
> ...
> COLn t1.COLn%TYPE
> );
>
> In above, the package pkg_A gets the values for the
> table name "T1" and column names COL1, COL2 .. COLn
> from a control table. A type declaration statement is
> created using the above values. The control table
> contains somthing like:
>
> Table_Name Column_Name
> T1 COL1
> T1 COL2
> ...
> T1 COLn
>
> The control table does not contain datatype for the
> columns (since it can be obtained from
> data-dictionary). We could, however, add another
> column to the control table, called "datatype", and
> populate it as a one-time activity, with the actual
> data types for these columns (it has to kept in sync
> with the DD).
>
> Thanks,
> Deepak
>
> --- "Mercadante, Thomas F (LABOR)"
> <Thomas.Mercadante_at_labor.state.ny.us> wrote:
>
> > Deepak,
> >
> > If that is the only place it references it, then
> > hard code the
> > declaration.
> >
> > If the col1 is a varchar2(10), then:
> >
> > col1 varchar2(100);
> >
> >
> > The *only* drawback here is if you ever change the
> > column datatype
> > declaration (like from vc2(10) to vc2(500)) then
> > your package may fail
> > when you move data into the local variable. Only
> > you can evaluate this
> > risk. Declaring the local variable larger than the
> > table declaration
> > would never cause you a problem.
> >
> > This, to me, looks like any easy decision to make!
> >
> > Good Luck!
> >
> > Tom
> >
>
> ____________________________________________________
> Yahoo! Sports
> Rekindle the Rivalries. Sign up for Fantasy Football
> http://football.fantasysports.yahoo.com
>

--
http://www.freelists.org/webpage/oracle-l