From oracle-l-bounce@freelists.org Wed Jun 29 03:24:10 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j5T8OA1Q023000 for ; Wed, 29 Jun 2005 03:24:10 -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 j5T8O6IP022985 for ; Wed, 29 Jun 2005 03:24:06 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E25B31C4B76; Wed, 29 Jun 2005 02:20:34 -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 03561-07; Wed, 29 Jun 2005 02:20:34 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5EDD41C5059; Wed, 29 Jun 2005 02:20:34 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=NOql6kaRtxMOGZPq5ma9ms3AWmyTeaORHw5XJUd8MmxslJ1y8xN/vu23eZDLr2eX2H96uB4O+kFbBBIpZEtXVVAVedgU6i9qpzcVaoRwzEK9VHv7xKkpCQCmnvuzWg3D0aOogaBTX7Xg4ItoeAPhnhJTIkJVwcwm2i7tkc3P7AM= Message-ID: Date: Wed, 29 Jun 2005 09:18:47 +0200 From: Eric Valk To: Deepak Sharma Subject: Re: Package becomes INVALID when a partiiton is dropped Cc: Thomas.Mercadante@labor.state.ny.us, oracle-l@freelists.org In-Reply-To: <20050628190403.38114.qmail@web31210.mail.mud.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Content-Disposition: inline References: <20050628190403.38114.qmail@web31210.mail.mud.yahoo.com> X-archive-position: 21800 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: listeric1@gmail.com Precedence: normal Reply-To: listeric1@gmail.com 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=-4.9 required=5.0 tests=BAYES_00 autolearn=ham version=2.63 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 : > 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)" > 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