Oracle APEX 20.1 Charts Error with “PL/SQL Function Body returning SQL Query”

Question

I have a problem with new charts in Oracle APEX 20.1.

I need to do chart from PL/SQL Function Body returning SQL Query, but it only works if I do it with some exact parameters. All variables return from the same submitted page.

If I use all variables as parameter then i get error:
ORA-20999: PL/SQL function body did not return a value.

If I use variable :P2_OBJECT and parameter for :P2_YEAR and :P2_ANNUAL_TIME then i get error:
ORA-20999: PL/SQL function body did not return a value.

If I use variable :P2_YEAR and parameter for :P2_OBJECT and :P2_ANNUAL_TIME then i get error:
ORA-20999: Parsing returned query results in "ORA-20999: Failed to parse SQL query! ORA-06550: line 2, column 402: ORA-00936: missing expression".

If I use variable :P2_ANNUAL_TIME and parameter for :P2_OBJECT and :P2_YEAR then i get error:
ORA-20999: Parsing returned query results in "ORA-20999: Failed to parse SQL query! ORA-06550: line 2, column 113: ORA-01741: illegal zero-length identifier".

but in a classic report the same function with variables works just fine...

this source for chart works:

declare l_sql varchar2(2000) ;
begin
select F_CHARTS(4020 , 2018 ,'TIM_MM',
:P2_LEVELS, :P2_SUB_LEVELS, :P2_SQL_CONDITION, :P2_WAREHOUSE, :P2_UNIT_OF_MEASURE)
into l_sql from dual;
return l_sql ; 
end ;

but this does not:

declare 
l_sql varchar2(2000) ;
begin
select F_CHARTS(:P2_OBJECT,:P2_YEAR,:P2_ANNUAL_TIME,
:P2_LEVELS, :P2_SUB_LEVELS, :P2_SQL_CONDITION, :P2_WAREHOUSE, :P2_UNIT_OF_MEASURE)
into l_sql from dual;
return l_sql ; 
end ;

This is the function:

create or replace FUNCTION F_CHARTS(
cod in NUMBER,      
year in NUMBER,         
t_time in varchar2,         
v_dims in varchar2,     
p_subl in varchar2,             
cod_filter in NUMBER,       
w_warehouse in varchar2,    
amount in NUMBER)        

return varchar2 is s varchar2(4000);

g2 boolean := false;
l NUMBER := 0; 
m VARCHAR2(1000) :='';
c VARCHAR2(40) :='';
w VARCHAR2(40) :='';
sql_cond VARCHAR2(4000) :='';
ope VARCHAR2(5) :='';

OGG_FACT_TAB VARCHAR2(40); 
OGG_COL_GROUP VARCHAR2(40); 
OGG_COL_SUBGR VARCHAR2(40); 
OGG_COL_SUM VARCHAR2(200); 
OGG_COL_TIME VARCHAR2(40); 
OGG_ALIAS_SUM VARCHAR2(100);
OGG_DIMS_TAB VARCHAR2(40); 
OGG_COL_KEY VARCHAR2(40); 
OGG_COL_DES VARCHAR2(40); 
OGG_ALIAS_TAB VARCHAR2(100); 
OGG_AVERAGES VARCHAR2(1);
OGG_CLASS NUMBER;
OGG_COL_SUM2 VARCHAR2(200); 

cursor times is SELECT QTM_DESCRIPTION,QTM_DES_VALUE,QTM_VALUE FROM Q_TIME WHERE QTM_FIELD_NAME=t_time ORDER BY QTM_SEQUENCE; 

BEGIN

select OGG_CLASS,OGG_FACT_TAB,OGG_COL_GROUP,OGG_COL_SUM,OGG_COL_TIME,OGG_ALIAS_SUM,OGG_DIMS_TAB,OGG_COL_KEY,OGG_COL_DES,OGG_ALIAS_TAB,OGG_COL_SUBGR,TRIM(OGG_AVERAGE),TRIM(OGG_COL_SUM2) into OGG_CLASS,OGG_FACT_TAB,OGG_COL_GROUP,OGG_COL_SUM,OGG_COL_TIME,OGG_ALIAS_SUM,OGG_DIMS_TAB,OGG_COL_KEY,OGG_COL_DES,OGG_ALIAS_TAB,OGG_COL_SUBGR,OGG_AVERAGES,OGG_COL_SUM2 from Q_OBJECT where OGG_CODE = cod;

IF ( p_subl is not null and p_subl <> 'null' and TRIM(p_subl) is not null and p_subl <> to_char(cod)
and OGG_COL_SUBGR IS not NULL and TRIM(OGG_COL_SUBGR) is not null ) THEN      
  g2:=true;
  IF ( OGG_CLASS = 1 AND amount = 1 AND OGG_COL_SUM2 IS NOT NULL ) THEN
    OGG_COL_SUBGR:='''kg''';    
    OGG_COL_SUM:=OGG_COL_SUM2;
  ELSE
    OGG_COL_SUBGR:='f.'||OGG_COL_SUBGR; 
  END IF;
END IF;

IF (OGG_AVERAGES is not null and OGG_AVERAGES='S') THEN
  ope:='avg'; 
ELSE
  ope:='sum';
END IF;

m:=t_time;
FOR reco IN times LOOP
  c:=reco.QTM_DESCRIPTION;
  m:=m||','||to_char(reco.QTM_VALUE)||','''||reco.QTM_DES_VALUE||'''';
END LOOP; 
m:=m||',''...'')';

s:='select null link, mm "'||c||'", ii "'||OGG_ALIAS_SUM||' '||to_char(year)||'" from ('; 
s:=s||'select '||t_time||' tt, decode(t.'||m||' mm';
s:=s||', trunc('||ope||' ('||OGG_COL_SUM||'))'||' ii';
s:=s||' from D_TIME t, '||OGG_FACT_TAB||' f';
s:=s||' where t.TIM_AAAA = '||year;
s:=s||' and f.'||OGG_COL_TIME||' = t.TIM_KEY';

IF (g2) THEN
  s:=s||' and  '||OGG_COL_SUBGR||' = '''||p_subl||'''';
END IF;

IF (OGG_FACT_TAB='R_MAG_SALES' and w_warehouse is not null and w_warehouse <> 'null') THEN 
  s:=s||' and f.MS_WAREHOUSE = '''||w_warehouse||'''';
END IF; 

IF (v_dims is not null and v_dims <> 'null') THEN 
  s:=s||' and f.'||OGG_COL_GROUP||' = '''||v_dims||'''';
END IF; 


BEGIN
  SELECT NVL(TRIM(SQL_CONDITION),'.') INTO sql_cond FROM Q_SQL_CONDITION WHERE SQL_CODE=cod_filter;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  sql_cond:='.';    
END;

IF (sql_cond<>'.') THEN
  s:=s||' and ('||sql_cond||')';
END IF;

s:=s||' group by t.'||t_time;
s:=s||' union ';
s:=s||'select t2.'||t_time||', decode(t2.'||m||', 0 from D_TIME t2';
s:=s||' where t2.'||t_time||' <> 0 and t2.'||t_time||' not in';
s:=s||' (select unique t3.'||t_time||' from D_TIME t3,'||OGG_FACT_TAB||' f3';
s:=s||'  where t3.TIM_AAAA = '||year;
s:=s||'  and t3.TIM_KEY = f3.'||OGG_COL_TIME;

BEGIN
  SELECT NVL(TRIM(SQL_CONDITION),'.') INTO sql_cond FROM Q_SQL_CONDITION WHERE SQL_CODE=cod_filter;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  sql_cond:='.';    
END;
IF (sql_cond<>'.') THEN
  s:=s||' and ('||sql_cond||')';
END IF;

IF (OGG_FACT_TAB='R_MAG_SALES' and w_warehouse is not null and w_warehouse <> 'null') THEN 
  s:=s||' and f3.MS_WAREHOUSE = '''||w_warehouse||'''';
END IF; 

IF (v_dims is not null and v_dims <> 'null') THEN 
  s:=s||'  and f3.'||OGG_COL_GROUP||' = '''||v_dims||''')';
ELSE
  s:=s||')';    
END IF; 

s:=s||' group by t2.'||t_time;
s:=s||')';
s:=s||' order by tt';

RETURN (s);

END;

To better understand this is the result of the function:

select null link, mm "Month", ii "  2018" from 
  (select TIM_MM tt, decode(t.TIM_MM,1,'January',2,'February',3,'March',4,'April',5,'May',6,'June',7,'July',8,'August',9,'September',10,'October',11,'November',12,'December','...') mm, trunc(sum (MS_FINAL_EXISTENCE)) ii from 
    D_TIME t, R_MAG_SALES f 
    where t.TIM_AAAA = 2018 and f.MS_TIM_BALANCE_DATE = t.TIM_KEY and  f.MS_UNIT_OF_MEASURE = 'PZ' and f.MS_COD_CATEGORY = '000001' group by t.TIM_MM 
  union select t2.TIM_MM, decode(t2.TIM_MM,1,'January',2,'February',3,'March',4,'April',5,'May',6,'June',7,'July',8,'August',9,'September',10,'October',11,'November',12,'December','...'), 0 from 
    D_TIME t2 
    where t2.TIM_MM <> 0 
    and t2.TIM_MM not in (select unique t3.TIM_MM from D_TIME t3,R_MAG_SALES f3  where t3.TIM_AAAA = 2018  and t3.TIM_KEY = f3.MS_TIM_BALANCE_DATE  and f3.MS_COD_CATEGORY = '000001') group by t2.TIM_MM) order by tt

Can enybody please help me, I just do not see what is wrong...

helloworld helloworld · Accepted Answer · 2020-06-23T20:25:42

When you write the query in IR/CR, the bind variables :P2_OBJECT,:P2_YEAR are null. So, your builder assumes the query as select F_CHARTS(NULL,NULL,....) into l_sql from dual; So the compilation of PLSQL block fails

You can write the query as select f_charts ( COALESCE(:P2_OBJECT, 'X'), COALESCE(:P2_YEAR, 0)....) from dual.

SO, you are actually sending some actual values to f_charts You can also handle this in function to assume some value if inputs are null.

Oracle APEX 20.1 Charts Error with “PL/SQL Function Body returning SQL Query”

1 Answers