RE: PL/SQL Procedures Get Invalidated

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Fri, 21 Apr 2006 13:30:38 -0400
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF4105071739CB@usahm236.amer.corp.eds.com>

Packages go invalid if you ALTER objects that the package is dependent on. Being that these are test databases could the problem be developers adding new columns to tables, modifying the length of columns, replacing stored procedures and functions, modifying the procedure parameter list in package procedures, etc.... In other words are you sure the problem is not normal development work going on?

Stupid design mistakes such as referencing a table created and dropped via dynamic SQL execute in one stored procedure from packages is another potential cause of problems like this. (I think I saw this last on metalink once.)

HTH -- Mark D Powell --

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sam Bootsma

	Sent: Friday, April 21, 2006 12:43 PM
	To: oracle-l_at_freelists.org
	Subject: PL/SQL Procedures Get Invalidated
	
	

	We have an inconsistent, but frequent, problem in our

non-production environment. Our web application, accessing a non-production database, will return a "page not found" error. The problem is always traced to one or more PL/SQL packages or procedures that are invalidated. Manually compiling the invalid object fixes the problem. Then, after a while, the package is invalidated again. The QA has also traced the source of the problem to clicking a back arrow on the web application (however, this does not always cause the problem, only sometimes).

This problem exists only for databases on our TEST box, and all these databases share the same Oracle Home on this box. The PROD database, by itself on a separate box, does not have this problem (whew!). I tried to get our QA or Systems Analyst to manually run the Oracle procedure from Sql*Plus, but I am told "the package can not be run directly from sql*plus since some build in package such as OWA, which is heavily depend on http context, is meant to be called from http request/response."

Can anybody provide me with a clue as to what is happening and what I can do to fix it? Why do we have packages that sometimes become invalidated?

We are running Oracle 9.2.0.6 on AIX 5.1 for all databases (TEST and PROD).

Thanks!

Sam Bootsma

Oracle Database Administrator

	Information Technology Services
	George Brown College

	Phone: 416-415-5000 x4933
	Fax: 416-415-4836
	E-mail: sbootsma_at_gbrownc.on.ca <mailto:sbootsma_at_gbrownc.on.ca>

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

Received on Fri Apr 21 2006 - 12:30:38 CDT