I was going to title this post “Hacking away at an APEX 3.2 app until you can wedge it into a 3.1 instance”, but I shortened it to the current title. This week I went to import an application I built in APEX 3.2 for an internal customer, only to find that their APEX instance was still on 3.1. They support a ton of mission critical apps on that instance so upgrading wasn’t an option, no matter how hard I begged. So, I began the tedious task of editing the export file until I was able to import it.
Just to be clear, this process is NOT SUPPORTED in any way, shape or form by Oracle or myself, so proceed with caution. DON’T EVEN THINK of calling support with issues introduced by this technique. If you start down this path, you are on your own! I’m posting this to try and help some of the more advanced developers in this community and I do not want to introduce a support nightmare for Oracle Support or the APEX Dev Team.
Import Hacking the Slow Way
I started by trying the import in SQL*Plus as it’s much faster and more informative than the web interface for repetitive imports. The key things that change from version to version of APEX are the procedures and their parameters of WWV_FLOW_API. Once I edited the SECURITY_GROUP_ID, SET_VERSION, and FLOW_ID (more on these later), I began the Import > Find Error > Edit File > Repeat sequence. One thing that really helps here is a text editor that supports split-screen, synchronized scrolling such as Notepad++ or UltraEdit. Notepad++ screenshot here. After a few minutes I came to the conclusion that there has to be a better way.
Import Hacking the Faster Way
I started by querying the ALL_ARGUMENTS view and saving the results in my own table:
create table apex_api_parameters as
select '3.2' version, object_name,argument_name
from all_arguments
where package_name='WWV_FLOW_API'
and owner = 'APEX_030200'
/
I repeated this step in each database to get the WWV_FLOW_API procedures and parameters for APEX 3.2 and 3.1. Now I can use SQL set operations to compare them:
select object_name
from apex_api_parameters
where version = '3.2'
minus
select object_name
from apex_api_parameters
where version = '3.1'
with common_objects as (
select object_name
from apex_api_parameters
where version = '3.2'
intersect
select object_name
from apex_api_parameters
where version = '3.1')
select p.object_name,p.argument_name
from apex_api_parameters p,common_objects c
where version = '3.2'
and p.object_name = c.object_name
minus
select p.object_name, p.argument_name
from apex_api_parameters p,common_objects c
where version = '3.1'
and p.object_name = c.object_name
Based on the results of the first query, I know the procedures in WWV_FLOW_API that exist in 3.2 but not in 3.1. These almost always equate to new features. If I were comparing 3.1 to 3.0 I would see the CREATE_WORKSHEET procedure which equates to interactive reports. The second query shows the parameters to procedures that exist in 3.2 but not in 3.1.
Step by Step Hacking
- Make a backup copy of the app you plan to edit.
- Using the APEX Application Builder in the newer (source) instance, edit the application to remove any major features that you know don’t exist in the older (target) instance, such as interactive reports.
- Export your application from the source instance.
- In the target instance, create a new, 1 page dummy application and export it.
- Use the SECURITY_GROUP_ID, SET_VERSION, and FLOW_ID from the dummy application in your actual application.
- Run my Parameter Diff application (below) and keep it open to use in the next two steps.
- Continue editing the application export file to remove any procedures that are not supported in the target instance.
- Now find all of the unsupported parameters for existing procedures and delete them.
- Test your import. Repeat steps 7-9 if needed.
To make this process easier, I created an APEX app with all of the API parameters from 3.2, 3.1, 3.0, and 2.2. You can see it in action here, or download it here. Keep in mind the supporting objects to 8,000+ inserts, so it may take a minute or two to install. Happy hacking!!!
