RE: Package becomes INVALID when a partiiton is dropped

From: Mercadante, Thomas F (LABOR) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Tue, 28 Jun 2005 14:37:22 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF16E75E@EXCNYSM0A1AJ.nysemail.nyenet>

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

-----Original Message-----
From: Deepak Sharma [mailto:sharmakdeep_oracle_at_yahoo.com] Sent: Tuesday, June 28, 2005 2:24 PM
To: Mercadante, Thomas F (LABOR); Eric List Cc: oracle-l_at_freelists.org
Subject: RE: Package becomes INVALID when a partiiton is dropped

Tom,

The package (actually one of the procedures witing it) references the table (T1) in variable declarations:

Example:

declare

col1 T1.col1%TYPE;
begin
...
end;
/

How would we do the declaration dynamically? Am I missing something?

Thanks,
Deepak

"Mercadante, Thomas F (LABOR)" <Thomas.Mercadante_at_labor.state.ny.us> wrote:

> Deepak,
>
> Something else you can do. Change your package so
> that it does not
> *directly* depend on the table. You could use
> dynamic SQL to perform
> your queries against the table. This way, Oracle
> does not know that the
> package is related to the table and will not mark it
> as invalid.
>
> Tom
>

Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250

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

Received on Tue Jun 28 2005 - 14:42:48 CDT