Forum OpenACS Improvement Proposals (TIPs): TIP #65: (Approved) Modify APM to recompile busted Oracle PACKAGE and PACKAGE BODY objects after upgrade
This fixed all but one or two out of dozens or a couple of hundred broken objects automatically, a great improvement.
My proposal is that the APM, after running an Oracle upgrade scripts, use Andrew's SQL code to fix any broken PAKCAGE and PACKAGE BODY objects (in that order) and report any errors (just as it does now when running *-create.sql scripts). This will greatly simplify the upgrade process for complex upgrades of many packages, especially for those who skip upgrades for some period of time then upgrade several versions at once.
(It certainly does try it for views. IIRC it does try it for package bodies as well)
The sript is located in $ORACLE_HOME/rdbms/admin/utlrp.sql
utlrp.sqlscript even existed. Looking at the version shipped with Oracle 18.104.22.168, it sounds as if that script will do the job - so that makes 3 entirely different scripts all available to solve this same problem.
However, the comments in utlrp.sql do specifically say that it must be run as Oracle user SYS, which is a very serious limitation. Someone should try it and tell us if that is in fact the case. If it is, it could probably be hacked to fix that, but you could also just use either Trezzo's or Kyte's scripts instead, which don't have that problem.
Btw, of the 3 solutions which should work, the only one I've ever actually used is Trezzo's recompile_all_objects. I found it first, about 4 years ago now, it worked just fine for me, and thus I never tried any of these others.
Essentially I've TIP'd this for Andrew, who's in Iceland at the moment on holiday. I don't - and I can't imagine Andrew does - care which script or query is used (it's very simple anyway).
The main point of the TIP is to have the APM do it for you automatically ...
Oracle automatically records dependencies among objects to alleviate the complex job of dependency management for the database administrator and users. For example, if you alter a table on which several stored procedures depend, Oracle automatically recompiles the dependent procedures the next time the procedures are referenced (executed or compiled against).
But nevermind, it probably says somewhere what the rules for recompilation on the fly are and how intricate it can get.
I'll try out utlrp.sql and the others and report back.
from obj$ o, user$ u
ERROR at line 25:
ORA-06550: line 25, column 17:
PLS-00201: identifier 'OBJ$' must be declared
[blah blah blah]