Oracle APEX – Dynamic Reporting

Introduction

Users can store multiple SQL queries in a database table, and these queries may include bind variables. When an end user selects a query, the application automatically identifies the required bind parameters, prompts the user to enter their values, and refreshes the report dynamically based on the provided inputs.

Why we need to do

In many Oracle APEX applications, report queries are hard coded by developers. Whenever business users need a new report or want to modify an existing query, they must request a developer to make the changes. This process is time-consuming and increases maintenance effort.

How do we solve

Step 1: Create the QUERY_MASTER Table
Create a table named QUERY_MASTER to store the SQL queries.

CREATE TABLE QUERY_MASTER
(
  QUERY_ID           NUMBER,
  QUERIES            CLOB,
  CREATE_BY          VARCHAR2(1000),
  CREATE_DATE        DATE,
  REPORT_NAME        VARCHAR2(4000),
  DYNAMIC_PARAMETER  VARCHAR2(10)
);

Step 2: Create the QUERY_PARAMETERS Table
Create another table named QUERY_PARAMETERS to store the parameter details for each query.

CREATE TABLE QUERY_PARAMETERS
(
  PARAMETER_ID   NUMBER,
  QUERY_ID       NUMBER,
  PARAMETER_NAME VARCHAR2(100) NOT NULL,
  DISPLAY_LABEL  VARCHAR2(255) NOT NULL,
  DISPLAY_TYPE   VARCHAR2(50) DEFAULT 'TEXT',
  PRIMARY KEY (PARAMETER_ID)
);

Step 3: Configure the Query Parameters
The QUERY_MASTER and QUERY_PARAMETERS tables are linked using the QUERY_ID column. The PARAMETER_NAME values in the QUERY_PARAMETERS table must exactly match the bind variable names used in the corresponding SQL query.

SELECT * FROM EMP WHERE DEPTNO = :DEPTNO;
-- Parameter_Name : DEPTNO

Step 4: Create the Report Selection List
Create a Select List page item (P3_SELECT) using the following SQL query.

SELECT DISTINCT
  REPORT_NAME d,
  QUERY_ID    r
FROM QUERY_MASTER;

Step 5: Create the Parameter Input Report
Create a Classic Report using the following SQL query. This report dynamically generates the input fields based on the selected report.

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%"')
    WHEN 'DATE' THEN
      apex_item.text(2, NULL, p_attributes => 'type="date" style="width:100%"')
    ELSE
      apex_item.text(2, NULL, p_attributes => 'style="width:100%"')
  END AS INPUT_FIELD
FROM QUERY_PARAMETERS
WHERE QUERY_ID = :P3_SELECT;

Step 6: Disable Escape Special Characters

Step 7: Refresh the Parameter Report
Create a Change Dynamic Action on the Select List item (P3_SELECT). Add a True Action of type Refresh and set the affected region to the parameter Classic Report.

Step 8: Store the Parameter Values
Create a Submit button and add the following PL/SQL code to its action (or process). This code stores all parameter names and values in an APEX collection.

BEGIN
  IF apex_collection.collection_exists('P5_PARAMS') THEN
    apex_collection.truncate_collection('P5_PARAMS');
  ELSE
    apex_collection.create_collection('P5_PARAMS');
  END IF;

  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),
      p_c002            => apex_application.g_f02(i)
    );
  END LOOP;
END;

Step 9: Create the Dynamic Report
Create another Classic Report and set its Source Type to PL/SQL Function Body Returning SQL Query.

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;

  SELECT queries INTO l_query
  FROM query_master
  WHERE query_id = :P3_SELECT;

  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 and set the number of generic columns to 60.

Step 11: Generate Dynamic Column Headings
Report Attributes → Heading → PL/SQL Function Body

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;

  SELECT queries INTO l_query
  FROM query_master
  WHERE query_id = :P3_SELECT;

  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;

  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);

  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
    RETURN 'Error';
END;

Step 12: Save and Run the application.

Conclusion

This solution enables end users to create and execute dynamic reports without requiring any application changes. By storing SQL queries and their corresponding parameters in database tables, the application can automatically generate input fields for bind variables and execute the selected query dynamically. This approach significantly reduces developer dependency, improves application flexibility, and allows business users to create customized reports on demand.

Recent Posts