Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Octagonal Peg in a Hexagonal Hole (APEX Backward Compatibility)

Posted by Tyler Muth on June 4, 2009

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

  1. Make a backup copy of the app you plan to edit.
  2. 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.
  3. Export your application from the source instance.
  4. In the target instance, create a new, 1 page dummy application and export it.
  5. Use the SECURITY_GROUP_ID, SET_VERSION, and FLOW_ID from the dummy application in your actual application.
  6. Run my Parameter Diff application (below) and keep it open to use in the next two steps.
  7. Continue editing the application export file to remove any procedures that are not supported in the target instance.
  8. Now find all of the unsupported parameters for existing procedures and delete them.
  9. 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!!!

9 Responses to “Octagonal Peg in a Hexagonal Hole (APEX Backward Compatibility)”

  1. I am definitely not a fan of encouraging customers who aren’t intimately familiar with APEX APIs and metadata to hacked their application export files. You’ve worked with APEX for years and years and you have access to the folks who can bail you out, if necessary. Most customers don’t have that luxury, and I can envision this only resulting in them calling Support, Support not being able to figure out where and what they tampered with, the customer being unhappy, and everyone losing.

    There is a reason why that compatibility check is in place. I don’t agree with encouraging customers to circumvent this, disclaimer or no disclaimer.

    Just my $0.02.

  2. matjazc said

    Perhaps customers finding themselves in similar circumstances will have a better chance to import 3.2->3.1 successfully now, without messing it up alone and calling Oracle support later.
    So everyone wins.

  3. Mathieu said

    Hello Tyler,

    Wasn’t it easier to rebuild the application in 3.1 instead of importing it?

    It is a bit tricky only to compare on argument and objectnames. If the same output argument differs in a theirs output (for example due to bugfixing), you wouldn’t have noticed during import. What if they have overloaded package functions/procedures? What if an argument changed from only input to input/output. Very risky. Maybe it works for 3.2 to 3.1, but as you said already it isn’t garanteed that 4.0 to 3.2 will work the same way.

    regards,

    Mathieu

  4. This is really cool but I wouldn’t recommend it to my peers.😉 I understand why it was a good solution for you.

    Thanks for sharing.

  5. Santhosh said

    Thanks a lot,

    It worked fine for me.

  6. Matt said

    Greate stuff mate, helped me out big time! To be honest reading the comments I’m surprised with some of the responses… people need articles like this as we’re always looking for solutions to problems and this is a big one, well it was for me, but its not anymore… cheers tyler!

  7. […] job easier and easier. The community work around the product is fantastic and a lifesaver at times. Tyler Muth had a great detailed posting on how to downgrade an APEX application and to top it off he even had […]

  8. santhosh said

    hi,

    now apex 4.0 is released, could you please get me the list of apex 4.0 to 3.2 or 3.1 compatibility….

    thanks in advance,
    Santhosh

  9. We have all listened to the old stating, but have you actually assumed about what would ultimately happen if you tried out to hammer a square peg into a spherical hole? For 1, you would get hold of the corners of the peg would get shaved off as it enters the hole. Following, you would see areas exactly where the sides of the peg really don’t contact the hole. Eventually, you may well break a great deal of pegs in the procedure.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: