From oracle-l-bounce@freelists.org Tue Jun 28 14:42:48 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j5SJgmoO007114 for ; Tue, 28 Jun 2005 14:42:48 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180] (may be forged)) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j5SJgeIP007080 for ; Tue, 28 Jun 2005 14:42:40 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5534A1C58D7; Tue, 28 Jun 2005 13:39:09 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 30550-10; Tue, 28 Jun 2005 13:39:09 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D10A71C58E6; Tue, 28 Jun 2005 13:39:08 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: RE: Package becomes INVALID when a partiiton is dropped Date: Tue, 28 Jun 2005 14:37:22 -0400 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Package becomes INVALID when a partiiton is dropped Thread-Index: AcV8DoD/yLYRvMeXQ+y+F8jqiIV0iAAAX69g From: "Mercadante, Thomas F (LABOR)" To: "Deepak Sharma" , "Eric List" Cc: X-OriginalArrivalTime: 28 Jun 2005 18:37:22.0902 (UTC) FILETIME=[6C58FF60:01C57C10] X-archive-position: 21772 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Thomas.Mercadante@labor.state.ny.us Precedence: normal Reply-To: Thomas.Mercadante@labor.state.ny.us X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-2.8 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 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@yahoo.com] Sent: Tuesday, June 28, 2005 2:24 PM To: Mercadante, Thomas F (LABOR); Eric List Cc: oracle-l@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)" 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