Custom SQL Report - Alternative to APEX built-in reports

Here is how you can create custom reports using PL/SQL region and jQuery. Report is capable of pagination and sorting in the column headings. This solution is an alternative to APEX built-in reports. It allows you control css and javascript more flexible. If you are using APEX 3.x, load jQuery library in page template header or page HTML header. Download and compile package to your application parsing schema. You may need change report styles in package procedure AJAX_REPORT.

Create On Demand process AJAX_REPORT_PPR:

report_util.ajax_report(
      pName     => APEX_APPLICATION.G_x01,
      pStartRow => APEX_APPLICATION.G_x02,
      pMaxRows  => APEX_APPLICATION.G_x03,
      pSortCol  => APEX_APPLICATION.G_x04,
      pSeqId    => APEX_APPLICATION.G_x05
    );
    

Create page process "Before Regions":

DECLARE
      l_name VARCHAR2(255);
    BEGIN
      l_name := 'CUSTOM_REPORTS';
      IF NOT APEX_COLLECTION.COLLECTION_EXISTS(p_collection_name => l_name) THEN
        APEX_COLLECTION.CREATE_COLLECTION(p_collection_name => l_name);
        APEX_COLLECTION.ADD_MEMBER(
          p_collection_name => l_name,
          p_clob001 => 'SELECT * FROM EMP', /* Report Query */
          p_c002    => 5,                   /* Max rows to display */
          p_c003    => 'EMPNO',             /* Sort column */
          p_c004    => 'ASC'                /* Sort order */
        );
        APEX_COLLECTION.ADD_MEMBER(
          p_collection_name => l_name,
          p_clob001 => 'SELECT * FROM DEPT',
          p_c003    => 'DEPTNO',
          p_c004    => 'ASC'
        );
      END IF;
      report_util.ajax_report_js;
    END;
    

Create two PL/SQL regions for reports. Region 1 source:

DECLARE
      l_name VARCHAR2(255);
    BEGIN
      l_name := 'CUSTOM_REPORTS';
      report_util.ajax_report(pName => l_name, pSeqId => 1);
    END;
    

Region 2 source:

DECLARE
      l_name VARCHAR2(255);
    BEGIN
      l_name := 'CUSTOM_REPORTS';
      report_util.ajax_report(pName => l_name, pSeqId => 2);
    END;