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!