APEXblog.nl - Tips and Tricks

About This Blog:
I (Richard Weug) started this blog primary for myself to save all kinds of Apex tips and tricks in one place. To use it as a place to write down how I used some coding in my own projects, but also to copy and paste all kinds of articles I find on the Internet. (So I never have to wonder on what website did I read about??? When I see something interesting I collect the content so I have my own archive/knowlegde base.

View my profile on LinkedIn


Installation & Configuration


Upgrading to APEX 4.1 on Oracle

Written by Richard Weug. Posted in Installation

I have been upgrading a lot (and I mean a lot) of APEX environments to 4.1 and never had any issues until ...
well until I encontered the infamous error : "ORA-06502: PL/SQL: numeric or value error: associative array shape is not consistent with session parameters".

Upgrading APEX is very simple. You just have to run the apexins.sql script and voila.
So the question is , what to you do if this script fails.

Well, first of all, you have to take backups before any upgrades like any other database upgrades. Better safe than sorry. Full rman database backups are better, rebuilding from a full export is a bit more tricky.

There is also a java class that comes with APEX that allows you to schedule backups of your applications. APEXExport does an  export of your applications by workspace or for the full instance. You can run that java class in a script file. You will find it in the APEX zip  under utilities / oracle / apex .

Back to my original story :). I ran the apexins.sql script to upgrade from APEX 3.1 to APEX 4.1 and got the ORA-06502 error.

I searched on google to find out that this is a bug with :

    Application Express 4.0 cannot be installed on Database version on Linux and potentially other platforms as well. The installation will end with "ORA-06502: PL/SQL: numeric or value error: associative array shape is not consistent with session parameters".
    Solution: Apply the patch for bug 5890966 (related bug) or apply the Patch 6 on Windows Platforms (if on Windows Platforms) or apply the database patch set or upgrade to 11gR1 or later.

I was upgrading a production environment and at this point my options were :

    restore database
    rollback to previous version of APEX
    apply the database patch and rerun the Upgrade

Option 1 was a last resort option. This would affect too many other systems, some not related to APEX. And it would take too much time.

Option 2, well, if you read the APEX documentation, it is usually easy to perform. (BTW I think that there is an error in the documentation , you probably have to run

exec flows_030100.wwv_flow_upgrade.switch_schemas


Instead of APEX_030200 ...) .

When you upgrade an APEX instance, it does not really upgrade. It creates a new schema called APEX_040100 (where 040100 is the version of APEX) and upgrades / copies the applications from the previous APEX schema. To rollback, you simply run a script that recreates APEX synonyms to point back to the previous version (wwv_flow_upgrade.switch_schemas). In that specific case, the procedure to switch back was a bit more complicated. Don't ask me why, it just is. And at the end, APEX would not be upgraded to 4.1 but back to 3.1.

Option 3, this really depended on the impact of the patch. Patching a production environment usually need some testing and planning. Fortunately, in our case, this patch was a minor patch. I contacted the DBA in charge and he agreed to apply it.

The question was:  If I re-run the apexins.sql script, will it know that the last valid install was APEX 3.1 and not the failed APEX 4.1 ? Can I just drop the APEX_040100 schema and re-run the apexins.sql script or do I have to rollback to the previous release before re-upgrading ?

The answer is : apexins.sql will check the latest version of APEX before upgrading. So you can drop the failed install schema (in this case APEX_040100) and re-run the upgrade without any worries.

I did just that and Bingo ! everything worked like a charm.

Lesson learned :

    When you upgrade APEX, make sure that you have good backups. Even if the upgrade is a very simple process and almost never fails. Sometimes it can fail due to Database issues like bugs or tablespaces being full etc.

    If the install/upgrade fails, you can drop the APEX schema and rerun the script.

Conclusion:  Better run APEX on and up  :)


Original article written by:  Francis Mignault | read the original article here