interactive report with dynamic sql statement

Question

I'm trying to generate an Interactive-Report in Apex with a SQL-Statement which lies in a page item (:P10_SQL), for example: select 1 from dual.

declare
 l_sql VARCHAR2(4000 CHAR); 
Begin
select V('P10_SQL') into l_sql from dual;
return l_sql;
End;

The Report has the type PL/SQL Function Body returning SQL Query. Apex shows an error ORA-20999: WWV_FLOW_EXEC.NULL_QUERY_RETURNED_BY_FUNCTION

Somebody gets an idea?

Answer 1

Apex acts kind of crazy as it doesn't always want to accept code I write (which is perfectly valid), saying that function doesn't return anything. Oh well.

Therefore, my function that returns SQL query was written in stages. The following bullets represents attempts I made; all of them are OK. The final version - the one that runs right now is as simple as return :P77_SQL; (I'm on page 77)

• return 'select dummy from dual'
  

• declare
    l_str varchar2(4000);
  begin
    l_str := case when :P77_SQL is null then 'select dummy from dual'
                  else :P77_SQL
             end;
    return l_str;
  end;
  

• return :P77_SQL;
  

However: you can't use interactive report, has to be a classic report because not all tables you'll have as a source will have the same column names (unless you use aliases and match number and datatypes, which is rather complicated).

But, if you use a classic report, then turn "Use generic column names" on and set desired number of generic columns (I set it to 10; you'll know what to do in your case).

My P77_SQL text item submits the page when I press ENTER, which causes the report to refresh.

A few examples: