RE: Package becomes INVALID when a partiiton is dropped

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Tue, 28 Jun 2005 12:04:02 -0700 (PDT)
Message-ID: <20050628190403.38114.qmail@web31210.mail.mud.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

Received on Tue Jun 28 2005 - 15:09:35 CDT