RE: Package becomes INVALID when a partiiton is dropped
- From: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
- To: Thomas.Mercadante@xxxxxxxxxxxxxxxxx, Eric List <listeric1@xxxxxxxxx>
- Date: Tue, 28 Jun 2005 12:04:02 -0700 (PDT)
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@xxxxxxxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
