Oracle APEX - Dynamic Reporting

Oracle APEX • PL/SQL • Dynamic Reporting

---

Have you ever needed to build a dashboard where users select a report from a dropdown list, and the page dynamically renders the query results? A common design pattern is to centralize your report queries in a database table:

REPORTS_TABLE (QUERY_ID, REPORT_NAME, QUERIES, CREATE_BY, CREATE_DATE)

However, if you've tried implementing this with an Interactive Report or Interactive Grid, you likely ran into database metadata conflicts or compilation errors when switching between reports.

In this post, we will explore why standard reports struggle with dynamic structures and provide a step-by-step guide to building a fully dynamic report viewer using Classic Reports and Generic Columns in Oracle APEX.

The Challenge: Fixed Metadata vs. Dynamic Columns

Oracle APEX Interactive Reports (IR) and Interactive Grids (IG) are highly interactive tools that allow end-users to sort, filter, and group data. To provide this functionality, APEX wraps your query in outer SQL statements. This requires APEX to know the exact column names, data types, and column counts at page design time.

If your stored queries return different columns (e.g., Query A returns 3 columns, Query B returns 8 columns), IR and IG will fail with ORA-00904: invalid identifier errors.

The Solution: Classic Reports combined with the Generic Columns option. This configuration maps runtime query columns to generic placeholders (COL01, COL02 ... COL60), allowing the page to render any valid SQL query on the fly.

Step-by-Step Implementation Guide

Step 1: Create the Select List Item

First, create a Select List item on your page (we'll name it P1_REPORT_SELECT) to let users choose which report to display.

• Type: Select List
• List of Values > Type: SQL Query
• SQL Query:

select report_name as display_value,
       query_id    as return_value
  from reports_table
 order by report_name;

Step 2: Create the Classic Report Region

Create a new region on your page where the dynamic data will be displayed.

• Title: Dynamic Report Viewer
• Type: Classic Report
• Source > Location: Local Database
• Source > Type: PL/SQL Function Body returning SQL Query
• PL/SQL Function Body:

declare
    l_query varchar2(32767);
begin
    -- Return a placeholder message if no report is selected
    if :P1_REPORT_SELECT is null then
        return 'select ''Please select a report from the list above.'' as message from dual';
    end if;

    -- Fetch the dynamic query text
    select queries
      into l_query
      from reports_table
     where query_id = :P1_REPORT_SELECT;

    return l_query;
exception
    when no_data_found then
        return 'select ''Error: Selected report query not found.'' as error_message from dual';
    when others then
        return 'select ''' || sqlerrm || ''' as error_message from dual';
end;

*Note: Remember to add P1_REPORT_SELECT to Page Items to Submit.

Step 3: Configure Generic Columns

1. Navigate to the Classic Report's Region tab in the Page Designer.
2. Scroll down to the Source section.
3. Set Use Generic Column Names to Yes.
4. Set Amount of Generic Columns to the maximum number of columns you expect your largest query to return (e.g., 60).

Step 4: Generate Dynamic Column Headers

Since APEX maps the columns to generic names like COL01, your table headers will display as "Col 1", "Col 2". To show the real headers, use `DBMS_SQL` to pull dynamic metadata:

1. Go to the Classic Report Attributes tab > Heading section.
2. Set Type to PL/SQL Function Body.
3. Enter this PL/SQL block:

declare
    l_query       varchar2(32767);
    l_cursor      integer;
    l_col_cnt     integer;
    l_desc_tab    dbms_sql.desc_tab;
    l_headers     varchar2(4000);
begin
    if :P1_REPORT_SELECT is null then
        return 'Message';
    end if;

    -- Fetch query text
    select queries
      into l_query
      from reports_table
     where query_id = :P1_REPORT_SELECT;

    -- Open cursor and describe the query columns
    l_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(l_cursor, l_query, dbms_sql.native);
    dbms_sql.describe_columns(l_cursor, l_col_cnt, l_desc_tab);
    dbms_sql.close_cursor(l_cursor);

    -- Concatenate column names separated by colons (:)
    for i in 1..l_col_cnt loop
        if i > 1 then
            l_headers := l_headers || ':';
        end if;
        l_headers := l_headers || l_desc_tab(i).col_name;
    end loop;

    return l_headers;
exception
    when others then
        if dbms_sql.is_open(l_cursor) then
            dbms_sql.close_cursor(l_cursor);
        end if;
        return 'Error';
end;

Step 5: Refresh the Report via AJAX

Make the UI responsive with a Dynamic Action so the report updates instantly when a new selection is made.

1. Right-click on P1_REPORT_SELECT and select Create Dynamic Action.
2. Name: Refresh Report
3. Event: Change
4. True Action > Action: Refresh
5. True Action > Selection Type: Region
6. True Action > Region: [Select your Classic Report Region]

Report Type Comparison Matrix

Here is a comparison of how different report types in Oracle APEX handle dynamic SQL structures:

Feature / Capability	Classic Report (Generic)	Interactive Report (IR)	Interactive Grid (IG)
Supports Dynamic Columns?	Yes	No	No
Dynamic Column Headers?	Yes (via PL/SQL)	No	No
End-User Filters & Sorting?	Manual Coding Only	Yes	Yes
Data Editing (DML)?	No	No	Yes

Security Warnings & Best Practices

WARNING: Executing raw queries stored in a database table introduces a significant SQL Injection risk. If unauthorized users gain access to modify your reports config table, they can write destructive database queries.

To keep your APEX application secure, implement these best practices:

• Authorization Schemes: Restrict the admin screen where queries are edited or created to high-privilege Developer/Admin roles only.
• SQL Parsing Validation: Add a database trigger or validation block to verify that the query text only starts with a SELECT token (and does not contain DDL operations like DROP or ALTER).
• Read-only DB Schema: Where possible, have the APEX parsing schema execute queries in a read-only context.

Conclusion

By using Classic Reports and the Generic Columns feature, APEX developers can bypass database compile-time design constraints and build highly flexible, dynamic dashboard tools. This minimizes page count, keeping your workspace clean and centralized.

▶ Live Demo