From: Steve Corbett Subject: Re: Invalid PL/SQL packages Date: 1997/06/06 Message-ID: <33977EF5.1532@fcs.wa.gov.au>#1/1 References: <5n6i3b$sg5@argon.btinternet.com> To: Keith Finnett Organization: CORCOM Systems Reply-To: stevec@fcs.wa.gov.au Newsgroups: comp.databases.oracle,comp.databases.oracle.tools packages, functions etc can be marked invalid if a table is dropped. if the table is recreated then the package, function etc should be automatically revalidated when invoked. however, this auto revalidate is not true for all Oracle tools, eg Discoverer 2000 just says it is invalid (in a most unfriendly manner)(Oracle have flagged this as an urgent product upgrade), but seems to be done for most eg SqlPlus. we came across this in out test Warehouse database as we drop and recreate tables there to refresh them from the operational test database. Our primary access to the warehouse is Discoverer 2000 and MS*Access, along with ReportWriter 1.1. One day a query in Discoverer 2000 would run, then the next day it wouldnt, unless ReportWriter had used the function and caused it to be revalidated. It would not have been a problem in our production Warehouse as we use truncate to clear the tables before refreshing them. Keith Finnett wrote: > > Is there any reason why a package created in PL/SQL would become invalid? > > I understand that if you are running a package from an Oracle Application > the package is validated on startup, but sometimes the package will become > invalid for no obvious reason. > > Any help would be appreciated. Please post and reply to keppy@lineone.net. > > Regards > > k