Why did my stored procedure stop working after upgrading Apex?

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

While most Apex applications will continue to work normally after upgrading Oracle Application Express to the latest version, there can be some applications that do not work as expected under all circumstances.
How do we know which applications are the most likely to fail?
This question can be easily answered: any apex installation based on EPG (the embedded PL/SQL gateway) is a very good candidate for troubles if you omit to migrate the contents of function WWV_FLOW_EPG_INCLUDE_MOD_LOCAL, located in the relevant apex schema of the version being upgraded.
Given the fact that Oracle 11G and Oracle XE are shipping with Apex and EPG, there should be quite an audience facing this problem sooner or later.

For instance, say you are upgrading from version 3.1.2 to 3.2.1: the current apex schema is FLOWS_030100, while the new schema is going to be APEX_030200.
Now, if you customized function FLOWS_030100.WWV_FLOW_EPG_INCLUDE_MOD_LOCAL in order to allow for certain stored procedures to be invoked from an URL, you must be aware that after upgrading Apex, the PL/SQL body of the function will be reset to the "factory" state, because Apex doesn't "merge" it with the modified version in the current apex schema.

Unfortunately Oracle Application Express Installation Guide doesn't mention or, better said, doesn't remind us to migrate this stuff manually from the previous version in case of upgrade, which can be done quite trivially by saving the function as a text file.
I think that it would be wise to include some reminder about this in the post-installation requirements, you know, sometimes we tend to forget something AS I JUST DID.
Probably it would be possible to issue some warning at the end of the upgrade after checking if the function had been modified by the user, a task for the Oracle Apex team :-)

Funnily enough the documentation encourages the administrator to drop the old apex schema once the upgrade has been successfully tested, in order to reclaim unused space, with the result of wiping out the customizations we did to WWV_FLOW_EPG_INCLUDE_MOD_LOCAL.

A typical symptom that there is something wrong with stored procedures invoked from URLs is when you get HTTP 403, as i described time ago, but in the case of procedures downloading images you won't probably see any explicit error, for instance Firefox was simply returning the ALTernate text for missing pictures (which was the ultimate reason as to why i began searching for this problem).

Not all was lost because i had backups and so on, but it's always a waste of time to restore a db just to find out which functions were previously authorized, assuming that you are not keeping a written record of these procedures elsewhere.

So here comes the fatal question: when do we decide if the apex upgrade was carried out successfully? Are we sure that we tested each and every nuance of our application(s), including those features that are probably not simply exercised by navigating through the pages?
A very typical usage for custom stored procedures, before the introduction of declarative blob support was indeed to provide download links for documents and images held in tables, which could mean that some old application may not work properly because we didn't take the time to rewrite those links using the new declarative approach. These cases are probably the most easy to spot because there will be something missing in the page. If custom PL/SQL procedures invoked from URLs are meant instead for submitting data, then they are more likely to be forgotten or overlooked during the testing phase because it's easy to think that they aren't touched by the apex upgrade.

So, is there any way to mitigate the problem or fix it for once?
I fear there isn't. The only sensible place for checking the problem is inside the installation/upgrade script and if it doesn't, then it is up to us to remember to do this extra step.

Hopefully the Apex team will come to the rescue in a near future.