Dynamic Report - Part 2

Dynamic Report

Introduction

In many Oracle APEX applications, different reports require different SQL queries. Instead of hard-coding these queries into the application, users can store multiple SQL queries in a database table. These queries may contain bind variables (for example, :P_DEPTNO or :P_EMPNO).

When a user selects a report, the application automatically:

• Identifies the bind variables used in the selected SQL query.
• Dynamically generates input fields for those parameters.
• Prompts the user to enter the required values.
• Executes the query using the provided inputs.
• Refreshes the report with the filtered results.

This approach allows a single report region to execute multiple dynamic queries without requiring any code changes.

Step-by-Step Implementation

Step 1

Create the QUERY_MASTER Table

This table acts as our repository for SQL queries. We will store the queries in a CLOB column to accommodate large reports, along with names and general descriptors.

CREATE TABLE QUERY_MASTER
(
    QUERY_ID           NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    QUERIES            CLOB NOT NULL,
    CREATE_BY          VARCHAR2(1000) DEFAULT NVL(V('APP_USER'), USER),
    CREATE_DATE        DATE DEFAULT SYSDATE,
    REPORT_NAME        VARCHAR2(4000) NOT NULL,
    DYNAMIC_PARAMETER  VARCHAR2(10)
);

Step 2

Create the QUERY_PARAMETERS Table

Next, we create a table to hold metadata about the parameter bindings. This links directly to the QUERY_MASTER table, allowing us to specify display labels and input formats (like Text, Number, or Date) for each parameter.

CREATE TABLE QUERY_PARAMETERS
(
    PARAMETER_ID   NUMBER GENERATED BY DEFAULT AS IDENTITY,
    QUERY_ID       NUMBER NOT NULL,
    PARAMETER_NAME VARCHAR2(100) NOT NULL,
    DISPLAY_LABEL  VARCHAR2(255) NOT NULL,
    DISPLAY_TYPE   VARCHAR2(50) DEFAULT 'TEXT',
    PRIMARY KEY (PARAMETER_ID),
    CONSTRAINT fk_qp_query_id FOREIGN KEY (QUERY_ID) REFERENCES QUERY_MASTER(QUERY_ID) ON DELETE CASCADE
);

Step 3

Configure the Query Parameters

For the engine to successfully resolve variables at runtime, the PARAMETER_NAME records must exactly match the bind variables defined in your SQL string.

💡

Binding Example: If your query is SELECT * FROM EMP WHERE DEPTNO = :DEPTNO AND SAL > :MIN_SALARY;, your parameter names in QUERY_PARAMETERS must be exactly DEPTNO and MIN_SALARY (case-sensitive).

Step 4

Create the Report Selection List

On your APEX page (e.g., Page 3), create a Page Item named P3_SELECT. Set its type to Select List and supply the following List of Values (LOV) query to display registered reports:

SELECT DISTINCT
       REPORT_NAME d,
       QUERY_ID    r
FROM QUERY_MASTER
ORDER BY REPORT_NAME;

Step 5

Create the Parameter Input Report

Create a Classic Report region on the page to display the input fields required for the chosen report. The following SQL utilizes apex_item to generate HTML input items on the fly:

SELECT
    DISPLAY_LABEL AS PARAMETER_LABEL,
    apex_item.hidden(1, PARAMETER_NAME) ||
    CASE DISPLAY_TYPE
        WHEN 'NUMBER' THEN
            apex_item.text(
                2,
                NULL,
                p_attributes => 'type="number" style="width:100%" class="apex-item-text"'
            )
        WHEN 'DATE' THEN
            apex_item.text(
                2,
                NULL,
                p_attributes => 'type="date" style="width:100%" class="apex-item-text"'
            )
        ELSE
            apex_item.text(
                2,
                NULL,
                p_attributes => 'style="width:100%" class="apex-item-text"'
            )
    END AS INPUT_FIELD
FROM QUERY_PARAMETERS
WHERE QUERY_ID = :P3_SELECT;

Step 6

Disable Escape Special Characters

By default, APEX escapes HTML to prevent XSS attacks. Because our SQL query in Step 5 generates HTML inputs using apex_item, we must allow this HTML to render in the browser.

⚠️

Configuration Action: Go to the Classic Report's column attributes. Select the INPUT_FIELD column, navigate to the Security settings in the property inspector, and turn OFF the "Escape Special Characters" toggle.

Step 7

Refresh the Parameter Report

We want the input form to adjust instantly when a user picks a different report. Create a Dynamic Action on the select item:

• Event: Change
• Selection Type: Item
• Item: P3_SELECT
• True Action: Refresh
• Affected Elements (Region): Choose the Parameter Input Classic Report created in Step 5.

Step 8

Store the Parameter Values in a Collection

Create a Submit Button. When clicked, we must capture the user's inputs from the array buffers (g_f01 maps to parameter names and g_f02 maps to values) and save them to a session-level APEX Collection named P5_PARAMS.

BEGIN
    -- Initialize or clear our session parameter store
    IF apex_collection.collection_exists('P5_PARAMS') THEN
        apex_collection.truncate_collection('P5_PARAMS');
    ELSE
        apex_collection.create_collection('P5_PARAMS');
    END IF;

    -- Loop through the submitted dynamic input elements
    FOR i IN 1 .. apex_application.g_f01.COUNT LOOP
        apex_collection.add_member(
            p_collection_name => 'P5_PARAMS',
            p_c001            => apex_application.g_f01(i), -- Parameter Name
            p_c002            => apex_application.g_f02(i)  -- Parameter Value
        );
    END LOOP;
END;

Step 9

Create the Classic Report

Create a new Classic Report region on your page. Set its Source Type to PL/SQL Function Body Returning SQL Query. This block fetches the raw SQL and uses regular expressions to swap bind variables with safe, dynamic select subqueries referencing our APEX Collection:

DECLARE
    l_query VARCHAR2(32767);
BEGIN
    IF :P3_SELECT IS NULL THEN
        RETURN 'select ''Please select a report from the list above.'' as message from dual';
    END IF;

    -- Fetch the configured query
    SELECT queries
      INTO l_query
      FROM query_master
     WHERE query_id = :P3_SELECT;

    -- Replace each bind variable with a collection lookup subquery
    FOR r IN (
        SELECT parameter_name
        FROM query_parameters
        WHERE query_id = :P3_SELECT
    ) LOOP
        l_query := REGEXP_REPLACE(
            l_query,
            ':' || r.parameter_name || '([^a-zA-Z0-9_$]|$)',
            '(select c002 from apex_collections where collection_name = ''P5_PARAMS'' and c001 = ''' || r.parameter_name || ''')\1',
            1,
            0,
            'i'
        );
    END LOOP;

    RETURN l_query;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 'select ''Report query not found.'' as error_message from dual';
    WHEN OTHERS THEN
        RETURN 'select ''' || apex_escape.html(SQLERRM) || ''' as error_message from dual';
END;

Step 10

Enable Generic Column Names

Because the query is parsed at runtime, the application builder does not know the columns beforehand. We must tell APEX to handle generic columns.

⚙️

Configuration Action: In the Classic Report , set Use Generic Column Names to Yes, and configure the Number of Generic Columns to 60 (or a number matching your widest query).

Step 11

Generate Dynamic Column Headings

Having headers like COL01, COL02 is terrible for user experience. To fix this, we will use the apex_exec API to parse the runtime SQL query, extract the true column names, and return them as a colon-delimited list for our headings.

Under your Dynamic Classic Report, navigate to Attributes ➔ Heading ➔ Type. Set it to PL/SQL Function Body and paste the following code:

DECLARE
    l_query    VARCHAR2(32767);
    l_context  apex_exec.t_context;
    l_columns  apex_exec.t_columns;
    l_headers  VARCHAR2(4000);
BEGIN
    IF :P3_SELECT IS NULL THEN
        RETURN 'Info';
    END IF;

    -- Fetch the raw SQL query
    SELECT queries
      INTO l_query
      FROM query_master
     WHERE query_id = :P3_SELECT;

    -- Substitute binds just like we did in Step 9
    FOR r IN (
        SELECT parameter_name
        FROM query_parameters
        WHERE query_id = :P3_SELECT
    ) LOOP
        l_query := REGEXP_REPLACE(
            l_query,
            ':' || r.parameter_name || '([^a-zA-Z0-9_$]|$)',
            '(select c002 from apex_collections where collection_name = ''P5_PARAMS'' and c001 = ''' || r.parameter_name || ''')\1',
            1,
            0,
            'i'
        );
    END LOOP;

    -- Open the context to evaluate query metadata
    l_context := apex_exec.open_query_context(
        p_location  => apex_exec.c_location_local_db,
        p_sql_query => l_query
    );

    l_columns := apex_exec.get_columns(l_context);

    -- Build the colon-delimited header list
    FOR i IN 1 .. l_columns.COUNT LOOP
        IF i > 1 THEN
            l_headers := l_headers || ':';
        END IF;
        l_headers := l_headers || l_columns(i).name;
    END LOOP;

    apex_exec.close(l_context);
    RETURN l_headers;
EXCEPTION
    WHEN OTHERS THEN
        IF l_context IS NOT NULL THEN
            apex_exec.close(l_context);
        END IF;
        RETURN 'Error Generating Headings';
END;

Step 12

Save and Run the Application

Save your modifications and run the page. Pick a report, input some test criteria into the dynamically generated parameters, click submit, and watch APEX populate your data with precise column labels. You have successfully implemented a dynamic SQL report generator!