Load more rows to report while scroll

Here is how create report that can be scrolled vertically. When user scrolls bottom of report it fetch more rows from database.

Copy your standard report template to new name. Edit new template and change Before Rows:

NOTE! If you use old or custom templates you need change table classes accordingly.

<table cellpadding="0" border="0" cellspacing="0" summary="" #REPORT_ATTRIBUTES# id="report_#REGION_STATIC_ID#">#TOP_PAGINATION#
<tr><td><div id="#REGION_STATIC_ID#_top">
<table cellpadding="0" border="0" cellspacing="0" summary="" class="report-standard"></table>
</div></td></tr>
<tr><td><div id="#REGION_STATIC_ID#_bottom">
<table cellpadding="0" border="0" cellspacing="0" summary="" class="report-standard">

Changes After Rows:

</table></div><div class="CVS">#EXTERNAL_LINK##CSV_LINK#</div></td></tr>
#PAGINATION#
</table>

Create classic report example from query:

SELECT e.EMPNO,
  e.ENAME,
  e.JOB,
  e.MGR,
  m.ENAME AS MNAME,
  e.HIREDATE,
  e.SAL,
  e.COMM,
  e.DEPTNO,
  d.DNAME,
  d.LOC
FROM emp e,
  dept d,
  emp m
WHERE e.deptno     = d.deptno
AND e.mgr          = m.empno(+)
  CONNECT BY LEVEL < 4
ORDER BY e.ENAME

Your query need have ORDER BY clause. Do not enable column heading sorting.

Edit report region and add to Region Footer:

<span role="alert" style="display:none;" id="apexir_LOADER">
<img src="#IMAGE_PREFIX#ws/ajax-loader.gif" alt="Processing Request" />
</span>

Edit Report attributes and change Report Template to your new template and Pagination Scheme to No Pagination Selected. Set Number of Rows to e.g. 50.

Create hidden item Px_START_ROW. Create before header computation for this item that always static assign value 1 for item.

Add to page JavaScript Function and Global Variable Declaration:

function setVscroll(pReqId,pHeight){
 var t=$("#"+pReqId+"_top");
 var b=$("#"+pReqId+"_bottom");
 b.find("table tr:first").children("th").each(function(i){
  var w=$(this).width();
  $(this).width(w);
  b.find("table tr:eq(1)").children("td").eq(i).width(w);
 });
 t.find("table").append(b.find("table tr:first")).find("tr").append(
  $("<th/>",{"class":"header","css":{"padding":0,"width":"16px","border-left":"1px solid #CCC"}})
 );
 t.find("table").css({"width":"100%"});
 b.find("table").css({"width":"100%"});
 b.css({"overflow-y":"scroll","height":pHeight,"border-bottom":"1px solid #CCC","overflow-x":"hidden"}).scrollTop(0).scroll(function(event){
  var self=$(this);
  var lTbl=self.children("table");
  if(self.scrollTop()>lTbl.height()-self.height()){
   $.ajax({
    type:"POST",
    url:"wwv_flow.show",
    dataType:"html",
    data:{
     p_flow_id:"&APP_ID.",
     p_flow_step_id:"&APP_PAGE_ID.",
     p_instance:"&APP_SESSION.",
     p_request:"APPLICATION_PROCESS=GET_MORE_ROWS",
     x01:pReqId.substr(1)
    },
    beforeSend:function(){
     /* Show loading image */
     $("#apexir_LOADER").show();
    },
    success:function(r){
     if(r.length===0){
      self.unbind("scroll");
     }else{
      lTbl.append(r);
     }
    },
    complete:function(){
     /* Hide loading image */
     $("#apexir_LOADER").hide();
    }
   });
  }
 });
}

Create dynamic Action. Select Advanced

• Name: Set report scroll
• Event: After Refresh
• Selection Type: Region
• Region: {select your report region}
• Condition: No Condition
• Action: Execute JavaScript code
• Fire On Page Load: True
• Code:

   setVscroll(this.triggeringElement.id,313);

• Selection Type: None

Dynamic action JavaScript second parameter is height in pixels you like use for report.

Create On Demand Process GET_MORE_ROWS:

DECLARE
  binds   DBMS_SQL.varchar2_table;
  desctab DBMS_SQL.DESC_TAB;
  curid   PLS_INTEGER;
  colcnt  PLS_INTEGER;
  l_start PLS_INTEGER;
  l_sta   PLS_INTEGER;
  l_end   PLS_INTEGER;
  l_more  PLS_INTEGER;
  l_val   VARCHAR2(4000); 
  l_col   VARCHAR2(4000);
  l_sql   VARCHAR2(32700);
BEGIN
  l_more := 50;
  l_start := nv('Px_START_ROW') + l_more;
  :Px_START_ROW := l_start;
  l_end := l_start + l_more;
  /* Get region query */
  SELECT region_source
  INTO l_sql
  FROM apex_application_page_regions
  WHERE application_id = :APP_ID
  AND page_id          = :APP_PAGE_ID
  AND region_id        = APEX_APPLICATION.G_X01
  ;
  /* Get bind variable names */
  /* NOTE! wwv_flow_utilities.get_binds is undocumented APEX internal function. We should not use it. */
  binds := wwv_flow_utilities.get_binds(l_sql);
  /* Build pagination query */
  l_sql := '
    SELECT * FROM (
      SELECT row_number() OVER (ORDER BY 1) AS "DERIVED$01",
        q.*
      FROM (' || l_sql || ') q
    ) WHERE "DERIVED$01" BETWEEN :B_START AND :B_END
  ';
  /* Open cursor and set where clause bind variables */
  curid := dbms_sql.open_cursor;
  dbms_sql.parse(curid, l_sql, dbms_sql.NATIVE);
  /* Set start and end row bind variables */
  dbms_sql.bind_variable(curid, ':B_START', l_start);
  dbms_sql.bind_variable(curid, ':B_END', l_end);
  /* Loop binds from region query and set value */
  FOR i IN 1 .. binds.COUNT LOOP
    dbms_sql.bind_variable(curid, binds(i), v(LTRIM(binds(i), ':')));
  END LOOP;
  dbms_sql.describe_columns(curid, colcnt, desctab);
  FOR i IN 2 .. desctab.count LOOP
    dbms_sql.define_column(curid, i, l_val, 4000);
  END LOOP;
  l_sta := dbms_sql.execute(curid);
  /* Loop query to output HTML for new rows */
  WHILE dbms_sql.fetch_rows(curid) > 0 LOOP
    htp.prn('<tr class="highlight-row">');
    FOR i IN 2 .. colcnt LOOP
      dbms_sql.column_value(curid, i, l_val);
      l_col := desctab(i).col_name;
      htp.prn( 
        '<td class="data" headers="' || l_col || '">' || l_val || '</td>');
    END LOOP;
    htp.prn('</tr>');
  END LOOP;
  dbms_sql.close_cursor(curid);
END;

NOTE! If you use old or custom templates you need change classes accordingly. Check classes from loop that output HTML for new rows. Change Px_START_ROW to code according your hidden item name.

See working example.