Exporting app from an old version of APEX

 

ORACLE APEX application export tip

Exporting app from an old version of APEX is possible!!!!!

In case you upgraded your APEX instance so that previous version of APEX is still available and you need to do an export for whatever reason you might read this post. 

 

I learned today that we can still export out all apps from an old APEX schema too. How we would do this? 

Our situation was that we upgraded instance to APEX 21.1.5 from APEX 20.02 a month or so ago and due to some research we needed to get the old APEX exports.

 

Theory goes, if we directly connect to the old apex schema (apex_200200), we should still be able to export the old application from the old APEX schema.

 

What is best is we can be using pure SQL to do this. This is link to APEX 20.02 API documentation

You may be familiar with the code above from earlier or newer versions of APEX but here it comes really handy.

 

An obvious example that come to my mind is -  instead of worrying will your app export be backward compatible maybe this can be an official workaround better than trying to manually hack it.

 

We had to tweak this code to make it work on APEX 200200 schema due to existing public synonyms in place so:

variable name varchar2(255)

variable contents clob

declare

  l_files apex_200200.wwv_flow_t_export_files;

begin

  l_files := apex_200200.wwv_flow_export_api.get_application( p_application_id => 100);

  :name := l_files(1).name;

  :contents := l_files(1).contents;

end;

/

set feed off echo off head off flush off termout off trimspool on

set long 100000000 longchunksize 32767

col name new_val name

select :name name from sys.dual;

spool &name.

print contents

spool off

Voilà!!! we have an app export from an old APEX version like we never upgraded it. 

 

In case you are using SQLcl or APEXExport method they may not work now because of already mentioned synonyms in place and clearly as some APIs have been updated between the two APEX versions.

To verify the story if you opened the exported file you would see that it looks like any good old export we did before. There should be a release reference to 20.02 version and not 21.01 which would normally happen in situation after the upgrade. 

Thanks to Patrick and Carsten from APEX team for sharing this tip. I am just sharing it here in case someone needs it in the future.
 

An interesting thing would be to figure out how far back you can take this but that is a material for another day. 

Happy APEXing,
Lino 

ORACLE APEX 21.1.5 chart upgrade issue

 

ORACLE APEX 21.1.5 chart upgrade issue

Function Body returning SQL Query - JavaScript error raised

This was reported to us recently and writing it here for reference only. 

 

Client's APEX environment got updated to APEX 21.1.5 from APEX 20.2 and they noticed on a simple report page that JS error started to appear: Ajax call returned server error ORA-06592: Case not found while executing CASE statement for .

 

To give more details, it was a simple page report parameters which filter out region that will be shown. 

 

Looking under the hood there were 32 charts but only charts that had PL/SQL Function Body returning SQL Query were getting this error. 

 

This is an example of one of the region codes that did not work:

 

Two strange things we noticed immediately. 

 

As soon as you would try to open this region source and Save validation of this region did not work.  

 

This is without changing anything. Normally this never happens or at least we never came across it. 

 

Secondly, chart series had a very strange configuration that was asked by APEX Builder to be updated when the page tried to be saved. 

 

Which is also something we did not encounter before. 

 

Unclear thing is why would APEX all of the sudden see this as an issue if this same source worked in previous version without any errors. 

 

Error had to do select .. into ... section which now was breaking this region source. Please keep in mind this hasn't been added now but was here before in APEX 20.2 and was working fine.   

 

This was the error trail in APEX:

Indicating that something behind the scene has been broken within internal APEX package. 

 

Looking into cause with help of Carsten and Patrick from APEX team we came to this conclusion. 

 

It looks like that (for some reason), the meta data was not upgraded correctly for this chart. With APEX 20.2 we introduced MLE support, and each code attribute got an additional attribute to identify the code language (PL/SQL or JavaScript). 

In your case it looks like this attribute was not set correctly during migration; the attribute is still NULL, thus the error.

To fix the problem, we had to update clients chart queries so that they parse and save without reporting any errors. 

 

Why would this issue arise from an upgrade is still unknown but at least we had only few region sources that needed our attention - lucky strike this time around. 

If it happens to you maybe this will be of help. 

 

Final comment to remember by Patrick: 

Page Designer has to actually execute your PL/SQL code already at design time to get a SQL query which is used to populate the columns. At that time NO session state is available, so PX_LOCATION will always be null.

You have to make sure that you either use an NVL with a value which is going to work or have an IF statement to branch to a code path where you can return a valid SQL Query. For example add an NVL to l_name to get a valid value while you are in Page Designer.

The goal is to get a parsable SQL Query.

In simple, this only means that queries must always validate in your region source. If you have if then blocks there also needs to be a default case that will help APEX return a valid SQL even when all inputs are null. Very often this confuses developers.

 

Happy APEXing,
Lino 

 

p.s. 20/01/2022 

 

For the reference official bug ref#: 

Bug 33764967 - FLASH5 CHART AUTO MIGRATION USING FUNCTION RETURNING SQL WILL RESULT IN CASE NOT FOUND   

 

 

p.s. There is an official workaround  for this

1. Export your app

2. Import your app overwriting the existing app

This will fix the upgrade problem. 

3. Make sure all your Function returning SQL regions have items to submit set else you might get NO DATA FOUND errors raised. 

Big thanks to Patrick and Carsten!