What's the best approach building a Oracle Apex Interactive report with user defined parameters?

Question

I am trying to generate a report in Oracle Apex with user's parameters . First i have one form where user chooses frequency, date, for whom the report to be and other options. I'm sending all this details to next page where it's present this Interactive report. What i have tried so far is building the query in PL/SQL Function body returning SQL Query but every single time I got the error below.

ORA-20999: WWV_FLOW_EXEC.NULL_QUERY_RETURNED_BY_FUNCTION

My code example:

declare
    single_date         char(12)  := ''''||TO_CHAR(TRUNC(to_date(:P21_DATE, 'DD-MM-YYYY')), 'DD.MM.YYYY')||'''';
    start_date_value    char(12)  := ''''||TO_CHAR(TRUNC(to_date(:P21_DATE, 'DD-MM-YYYY'), 'IW'), 'DD.MM.YYYY')||'''';
    end_date_value      char(12)  := ''''||TO_CHAR(TRUNC(to_date(:P21_DATE, 'DD-MM-YYYY'), 'IW') + 6, 'DD.MM.YYYY')||'''';
    first_month_value   char(12)  := ''''||to_char(trunc(to_date(:P21_DATE, 'DD-MM-YYYY'), 'MM'), 'DD.MM.YYYY')||'''';
    last_month_value    char(12)  := ''''||to_char(LAST_DAY(to_date(:P21_DATE,'DD-MM-YYYY')), 'DD.MM.YYYY')||'''';
    first_year_value    char(12)  := ''''||to_char(trunc(to_date(:P21_DATE, 'DD-MM-YYYY'), 'YYYY'), 'DD.MM.YYYY')||'''';
    last_year_value     char(12)  := ''''||to_char(last_day(add_months(to_date(:P21_DATE,'DD-MM-YYYY'),12 - to_number(to_char(to_date(:P21_DATE,'DD-MM-YYYY'),'mm')))), 'DD.MM.YYYY')||'''';
begin
    if :P21_DETAILS = 1 then
        if :P21_FREQUENCY = 1 then
            if :P21_FOR = 1 then
                return q'~
                SELECT <columns> FROM <table> WHERE <date> = '||single_date||' AND <something> = '||lower(:APP_USER)||';
                ~';
            elsif :P21_FOR = 2 then
                return q'~
                SELECT <columns> FROM <table> WHERE <date> = '||single_date||' AND <something> = '||lower(:P21_PERSON)||';
                ~';
            elsif :P21_FOR = 3 then
                return q'~
                SELECT <columns> FROM <table> WHERE <date> = '||single_date||';
                ~';
            end if;
        elsif :P21_FREQUENCY = 2 then
            if :P21_FOR = 1 then
                return q'~
                SELECT <columns> FROM <table> WHERE <date> BETWEEN TO_DATE('||start_date_value||', ''DD.MM.YYYY'') AND TO_DATE('||end_date_value||', ''DD.MM.YYYY'') AND <something> = '||lower(:APP_USER)||';
                ~';
            elsif :P21_FOR = 2 then
                return q'~
                SELECT <columns> FROM <table> WHERE <date> BETWEEN TO_DATE('||start_date_value||', ''DD.MM.YYYY'') AND TO_DATE('||end_date_value||', ''DD.MM.YYYY'') AND <something> = '||lower(:P21_PERSON)||';
                ~';
            elsif :P21_FOR = 3 then
                return q'~
                SELECT <columns> FROM <table> WHERE <date> BETWEEN TO_DATE('||start_date_value||', ''DD.MM.YYYY'') AND TO_DATE('||end_date_value||', ''DD.MM.YYYY'');
                ~';
            end if;
        elsif :P21_FREQUENCY = 3 then
            if :P21_FOR = 1 then
                return q'~
                SELECT <columns> FROM <table> WHERE <date> BETWEEN TO_DATE('||first_month_value||', ''DD.MM.YYYY'') AND TO_DATE('||last_month_value||', ''DD.MM.YYYY'') AND <something> = '||lower(:APP_USER)||';
                ~';
            elsif :P21_FOR = 2 then
                return q'~
                SELECT <columns> FROM <table> WHERE <date> BETWEEN TO_DATE('||first_month_value||', ''DD.MM.YYYY'') AND TO_DATE('||last_month_value||', ''DD.MM.YYYY'') AND <something> = '||lower(:P21_PERSON)||';
                ~';
            elsif :P21_FOR = 3 then
                return q'~
                SELECT <columns> FROM <table> WHERE <date> BETWEEN TO_DATE('||first_month_value||', ''DD.MM.YYYY'') AND TO_DATE('||last_month_value||', ''DD.MM.YYYY'');
                ~';
            end if;
        elsif :P21_FREQUENCY = 4 then
            if :P21_FOR = 1 then
                return q'~
                SELECT <columns> FROM <table> WHERE <date> BETWEEN TO_DATE('||first_year_value||', ''DD.MM.YYYY'') AND TO_DATE('||last_year_value||', ''DD.MM.YYYY'') AND <something> = '||lower(:APP_USER)||';
                ~';
            elsif :P21_FOR = 2 then
                return q'~
                SELECT <columns> FROM <table> WHERE <date> BETWEEN TO_DATE('||first_year_value||', ''DD.MM.YYYY'') AND TO_DATE('||last_year_value||', ''DD.MM.YYYY'') AND <something> = '||lower(:P21_PERSON)||';
                ~';
            elsif :P21_FOR = 3 then
                return q'~
                SELECT <columns> FROM <table> WHERE <date> BETWEEN TO_DATE('||first_year_value||', ''DD.MM.YYYY'') AND TO_DATE('||last_year_value||', ''DD.MM.YYYY'');
                ~';
            end if;
        end if;
    end if;
end;

Tried also other ways, code below but no success also.

ORA-01403: no data found

declare
    date_modify         char(12)  := :P24_DATE;
    single_date         char(12)  := ''''||TO_CHAR(TRUNC(to_date(date_modify, 'DD.MM.YYYY')), 'DD.MM.YYYY')||'''';
    start_date_value    char(12)  := ''''||TO_CHAR(TRUNC(to_date(date_modify, 'DD.MM.YYYY'), 'IW'), 'DD.MM.YYYY')||'''';
    end_date_value      char(12)  := ''''||TO_CHAR(TRUNC(to_date(date_modify, 'DD.MM.YYYY'), 'IW') + 6, 'DD.MM.YYYY')||'''';
    first_month_value   char(12)  := ''''||to_char(trunc(to_date(date_modify, 'DD.MM.YYYY'), 'MM'), 'DD.MM.YYYY')||'''';
    last_month_value    char(12)  := ''''||to_char(LAST_DAY(to_date(date_modify,'DD.MM.YYYY')), 'DD.MM.YYYY')||'''';
    first_year_value    char(12)  := ''''||to_char(trunc(to_date(date_modify, 'DD.MM.YYYY'), 'YYYY'), 'DD.MM.YYYY')||'''';
    last_year_value     char(12)  := ''''||to_char(last_day(add_months(to_date(date_modify,'DD.MM.YYYY'),12 - to_number(to_char(to_date(date_modify,'DD.MM.YYYY'),'mm')))), 'DD.MM.YYYY')||'''';
    query               varchar2(500);
begin
    if apex_application.g_f01(1) = 1 then
        if :P24_TYPE = 1 then
            query := 'SELECT <some columns> FROM <table> WHERE <date> = '||single_date||'';
        elsif :P24_TYPE = 2 then
            query := 'SELECT <some columns> FROM <table> WHERE <date> BETWEEN TO_DATE('||start_date_value||', ''DD.MM.YYYY'') AND TO_DATE('||end_date_value||', ''DD.MM.YYYY'')';
        elsif :P24_TYPE = 3 then
            query := 'SELECT <some columns> FROM <table> WHERE <date> BETWEEN TO_DATE('||first_month_value||', ''DD.MM.YYYY'') AND TO_DATE('||last_month_value||', ''DD.MM.YYYY'')';
        elsif :P24_TYPE = 4 then
            query := 'SELECT <some columns> FROM <table> WHERE <date> BETWEEN TO_DATE('||first_year_value||', ''DD.MM.YYYY'') AND TO_DATE('||last_year_value||', ''DD.MM.YYYY'')';
        end if;
    end if;
    if :P24_PERSON <> 'no' then
        query := query||' AND <something_else> = '''||lower(:P24_PERSON)||'''';
    end if;

    return query;
end;

Thanks in advance.

You are not handling other cases - e.g. if :P21_DETAILS != 1 no query will be built, leading to the error you get. — Tony Andrews

Scott Scott · Accepted Answer · 2018-07-06T03:24:56

Looking past and logic problems, your code is currently a SQL injection nightmare, leaving your page vulnerable to users querying information they're not allowed to. It's also forcing usage of literals, which will blow away your shared SQL too quickly, forcing hard parsing.

There is native functionality that allows you to link to a page with an IR, and prepopulate filters as if you were using them manually.

https://docs.oracle.com/database/apex-5.1/HTMDB/linking-to-interactive-reports.htm#HTMDB30108

What's the best approach building a Oracle Apex Interactive report with user defined parameters?

1 Answers