ORACLE APEX 5.1.1: Chart based on PL/SQL Function shows values in wrong label

Question

I'm using ORACLE APEX 5.1.1 and want to show a chart based on "PL/SQL Function Body returning SQL Query". The function changes the SQL-Query based on the value of a Select List. So the Chart should change whenever the Select List gets changed. I have set up all the queries and Dynamic Actions needed and the functionality works just fine. But the Chart displays the values in the wrong "Label slots" (categories on the x-axis). To clarify the wrong chart display I also created a classic report based on the exact same function, which displays the value correctly.

I replicated a mininmal example on my evaluation account on https://apex.oracle.com. There everything is just the way it's supposed to be. apex.oracle.com is currently at version 5.1.3. So I'm wondering whether this is a bug in version 5.1.1 that got fixed. I can't find anything about this issue in the release notes, though. Does anyone know about this bug or can help me with my issue in 5.1.1? Unfortunately, I can't upgrade to 5.1.3 in my corporate environment for now.

Here is the PL/SQL I use for both, the classic report and the chart:

declare
  q varchar2(32767);

begin
  q :=
'
select
    type1, type2, sum(to_number(val)) as val
from (
SELECT ''a'' as type1, ''x'' as type2, ''1'' as val
  FROM DUAL
UNION ALL
SELECT ''a'' as type1, ''y'' as type2, ''2'' as val
  FROM DUAL
UNION ALL
SELECT ''a'' as type1, ''z'' as type2, ''3'' as val
  FROM DUAL
UNION ALL
SELECT ''b'' as type1, ''x'' as type2, ''3'' as val
  FROM DUAL
UNION ALL
SELECT ''b'' as type1, ''z'' as type2, ''3'' as val
  FROM DUAL
  UNION ALL
SELECT ''c'' as type1, ''x'' as type2, ''2'' as val
  FROM DUAL
  UNION ALL
SELECT ''c'' as type1, ''y'' as type2, ''-2'' as val
  FROM DUAL
  UNION ALL
SELECT ''d'' as type1, ''y'' as type2, ''-3'' as val
  FROM DUAL
  UNION ALL
SELECT ''d'' as type1, ''z'' as type2, ''1'' as val
  FROM DUAL
UNION ALL
SELECT ''e'' as type1, ''x'' as type2, ''1'' as val
  FROM DUAL
UNION ALL
SELECT ''a'' as type1, ''z'' as type2, ''3'' as val
  FROM DUAL
)
'
;
  if :TEST_VALS = 'only positive' then q := q || ' where val >= 0';
  elsif :TEST_VALS = 'only negative' then q := q || ' where val <= 0';  
  end if;
  q := q || ' group by type1, type2 order by type1, type2';
  return q;
end;

:TEST_VALS refers to a static Select List with the values all, only positive, only negative. The Dynamic actions are set up with Refresh on Change at the Select List and "Page Items to Submit" at the classic report and the chart.

Below you see 3 screenshots showing the bug in 5.1.1 and the correct chart display in 5.1.3.

Wrong chart with 5.1.1 and selection "all":

Wrong chart with 5.1.1 and selection "only positive values":

Correct chart with 5.1.3 (on web evaluation account):

Newton Newton · Accepted Answer · 2017-11-08T11:05:42

Eventually, I found the answer myself here: https://community.oracle.com/thread/4069783

The problem didn't have anything to do with the PL/SQL Function. Prior to version 5.1.3 of ORACLE APEX any Chart requires the same number of data points to exist in each series. So, you need to add data densification in your query yourself. I agree with the following quote of an ORACLE developer that this wasn't optimal...

For 5.1, we intentionally left the 'densification' of the data to the customer to handle via their SQL query. However, this has led to some confusion and numerous questions on the forum and at conferences, so we have revised that behaviour for our upcoming 5.1.3 patch set release. In that release, we will now automatically handle the injection of missing data points, to ensure that the resulting JSON object meets JET's requirement of having the same number of data points, in each series of a multi-series chart, for each x-axis label. I can't provide details of when 5.1.3 will be made available, so for now you can manage the densification in your query.

ORACLE APEX 5.1.1: Chart based on PL/SQL Function shows values in wrong label

1 Answers