Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: PL/SQL Procedures Get Invalidated
Our developers work on DEV database ... they don't touch the TEST
database, except for selects. This problem has also been reproduced on
a database that is very lightly used. Thanks for the suggestions
though.
I think I know what you are saying in the second paragraph. If procedure A references table T, but table T is dynamically created and dropped in procedure B, then procedure A could be invalidated just by running procedure B. But we have the exact same code in PROD, and the problem does not occur there, so I don't think that is the problem.
More suggestions/ideas welcomed.
Thanks,
Sam.
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark D
Sent: Friday, April 21, 2006 1:31 PM
To: oracle-l_at_freelists.org
Subject: RE: PL/SQL Procedures Get Invalidated
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 ournon-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-lReceived on Fri Apr 21 2006 - 12:49:04 CDT