ORA-20001: Query must begin with SELECT or WITH

Question

I have a pl/sql function body returning and sql query for a jets chart:

declare

    q varchar2(32767);
    type series is table of varchar(32);
    l_series series;

begin
    select 
    column1 bulk collect into l_series 
    from table1
    where column1 in (select column1 
                            from table2
                            where trunc(req_date, decode(:P1_FILTER_BY,'Week','W','Mon')) >= decode(:P1_FILTER_BY, 'Week', (to_date(:P1_DATE, 'DD-Mon-YYYY') - 42), (to_date(:P1_DATE, 'DD-Mon-YYYY') - 180))
                            and trunc(req_date, decode(:P1_FILTER_BY,'Week','W','Mon')) <= to_date(:P1_DATE ,'DD-Mon-YYYY')
    );
    
    for i in 1..l_series.count loop 
        q := q ||
        'select to_char(trunc(req_date, decode(:P1_FILTER_BY,''Week'',''W'',''Mon'')),decode(:P1_FILTER_BY,''Week'',''DD-Mon-YYYY'',''YYYY-MM'')) as label,
    count(*)   as value,
    (select ''' || l_series(i)  || ''' from dual) as series,
    trunc(req_date, decode(:P1_FILTER_BY,''Week'',''W'',''Mon'')) sortby
    from table2
    where column1 = ''' || l_series(i)  || ''' 
    and trunc(req_date, decode(:P1_FILTER_BY,''Week'',''W'',''Mon'')) >= decode(:P1_FILTER_BY,''Week'',to_date(:P1_DATE, ''DD-Mon-YYYY'') - 42,to_date(:P1_DATE, ''DD-Mon-YYYY'') - 180)
    and trunc(req_date, decode(:P1_FILTER_BY,''Week'',''W'',''Mon'')) <= to_date(:P1_DATE, ''DD-Mon-YYYY'')
    group by  trunc(req_date, decode(:P1_FILTER_BY,''Week'',''W'',''Mon''))';

        if( i != l_series.count) then
            q := q || ' 
            union all ';
        end if;

    end loop;
    
    q := q || 'order by sortby';
    
    return q;
    
    exception when no_data_found then return null;

end;

If I run the pl/sql like above I get the error

ORA-20001: Query must begin with SELECT or WITH

However if I change the :P1_DATE in the where clause in two spots from :P1_DATE to '01-Jun-2023':

where trunc(req_date, decode(:P1_FILTER_BY,'Week','W','Mon')) >= decode(:P1_FILTER_BY, 'Week', (to_date(:P1_DATE, 'DD-Mon-YYYY') - 42), (to_date('01-Jun-2023', 'DD-Mon-YYYY') - 180))
and trunc(req_date, decode(:P1_FILTER_BY,'Week','W','Mon')) <= to_date('01-Jun-2023' ,'DD-Mon-YYYY'));

I get no error.

At first I thought it was :P1_DATE, which is a select list with the LOV:

select distinct
to_char(trunc(req_date, decode(:P1_FILTER_BY,'Week','W','Mon')),'DD-Mon-YYYY') d,
to_char(trunc(req_date, decode(:P1_FILTER_BY,'Week','W','Mon')),'DD-Mon-YYYY') l
from table2
order by 2 desc

But that doesn't seem to be the issue as it works for one part of the where clause but not the other.

Answer 1

For source types "Function Body returning SQL Query", the apex engine will validate the pl/sql block when the region is saved in the builder and check if a valid query is returned. So if there are bind variables (like :P1_DATE) in the pl/sql block, those will not be bound correctly (since this is in the builder and not runtime) and the block will not compile. To work around this, make sure that all bind variables have a default value in the function body.