Classic Report with Dynamic SQL Statement Using Oracle Apex

I have a table with 2 columns, Query_Id and Sql_Stmt.

| Qry_Id | Sql_Stmt                          |
| -------| ----------------------------      |
| 1      | select empno,empname,sal from emp;|
| 2      | select deptno,location from dept; |        

Requirement is that if I select query id 1, then the corresponding sql statement should execute and show output via classic or interactive report in a modal/non modal page.

Below code used at classic report source "PL/SQL Function Body returning SQL Query":

DECLARE
    l_sql CLOB;
BEGIN
    SELECT Sql_Stmt INTO l_sql FROM SQL_QUERY_TAB where QRY_ID=:P1_ID ;
    RETURN l_sql;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RETURN 'select * from dual'; 
END;

But I am not getting required output, instead getting this error in report region: ORA-00904: "D"."DUMMY": invalid identifier

What version of APEX are you on? I tried the following, in a classic report on APEX 24.2. This is a classic report with type "PL/SQL Function Body returning SQL Query":

Code:

DECLARE
  l_stmt VARCHAR2(1000);
BEGIN
    WITH my_queries (id, sql_stmt) AS 
    (
    SELECT 1, 'select empno, ename, sal from emp' FROM dual UNION ALL
    SELECT 2, 'select deptno, dname from dept' FROM DUAL
    )
    SELECT sql_stmt 
      INTO l_stmt 
      FROM my_queries 
     WHERE id = 3;
    RETURN l_stmt;
EXCEPTION WHEN NO_DATA_FOUND THEN 
    -- use 1 = 2 to get no rows but make the report run fine.
    RETURN q'!SELECT 'invalid query' as result FROM DUAL WHERE 1 = 1!';
END;

Result:

When I change the RETURN clause in the EXCEPTION block to SELECT * FROM DUAL is also works fine.

update

When testing this further using a page item in my pl/sql block I ran into the same error. The solution is to use the "Generic Columns" attribute in the report.

alternative solution

Here is an alternative solution to work around this error. Note that this won't work if the report region is refreshed with a dynamic action.

On the classic report put a serverside condition of type "Rows Returned" and Query SELECT Sql_Stmt FROM SQL_QUERY_TAB where QRY_ID=:P1_ID

Create a new region of type static with a message that the report doesn't exist with the opposite serverside condition of type "No Rows Returned" and Query SELECT Sql_Stmt FROM SQL_QUERY_TAB where QRY_ID=:P1_ID

That way, the NO_DATA_FOUND exception will never be raised since it is caught in the serverside exception.