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:<br> 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:<br> 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:<br> 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:<br> 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...