Select All records in Interactive Report without any limitation Oracle APEX

How to select all records in Interactive Report without any limitation Oracle APEX

This approach can be used in case of when a single pagination has more than 50 records.

1. Create a table which has large no of records i.e. INDIA_PINCODE.

2. Create a APEX_COLLECTION process before header position

BEGIN
  IF NOT APEX_COLLECTION.COLLECTION_EXISTS('CHK_COL') THEN
    APEX_COLLECTION.CREATE_COLLECTION('CHK_COL');
  ELSE
    APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION('CHK_COL');
  END IF;
END;

4. Create a Interactive report on above mentioned table and give static_id "myreport"

select apex_item.checkbox(1, PINCODE, CASE
       WHEN col.n001 IS NOT NULL THEN 'checked'
       END || ' chkid="' || PINCODE || '"', NULL, NULL, 'chkbox') "Select",
       ID,
       POSTOFFICENAME,
       PINCODE,
       DISTRICTSNAME,
       CITY,
       STATE
  from INDIA_PINCODE ip,
  (SELECT n001 FROM apex_collections WHERE collection_name = 'CHK_COL') col
  where ip.PINCODE = col.n001 (+)

5. Change Heading of Select Column and paste below code

<input type="checkbox" id="selectunselectall"> 

6. Create a Ajax Callback process named  SELECT_UNSELECT

DECLARE
  l_count    NUMBER;
  l_mode     VARCHAR2(20) := APEX_APPLICATION.G_X01;
BEGIN
IF l_mode='checked'
THEN
   l_count := apex_application.g_f01.count;
   FOR i IN 1..apex_application.g_f01.count
   LOOP
      select count(1) into l_count from apex_collections where collection_name = 'CHK_COL' and n001 = apex_application.g_f01(i);
      if l_count = 0
      then
        APEX_COLLECTION.ADD_MEMBER(
          p_collection_name => 'CHK_COL',
          p_n001 => apex_application.g_f01(i));
      end if;
   END LOOP;
ELSIF l_mode = 'unchecked'
THEN
    FOR i IN 1..apex_application.g_f02.count
    LOOP
        FOR c1 IN (select * from apex_collections where collection_name = 'CHK_COL' and n001 = apex_application.g_f02(i))
        LOOP
            APEX_COLLECTION.DELETE_MEMBER(
              p_collection_name => 'CHK_COL',
              p_seq => c1.seq_id);
        END LOOP;
    END LOOP;
END IF;
    apex_json.open_object;
      apex_json.write(
        p_name => 'result',
        p_value => 'success'
      );
      apex_json.close_object;
    exception
      when others then
        apex_json.open_object;
        apex_json.write(
          p_name => 'result',
          p_value => SQLERRM
        );
        apex_json.close_object;
END;

7. Create a Dynamic Action
   

Name	Select all
Event	Change
Selection Type	jQuery Selector
jQuery Selector	#selectunselectall
Event Scope	Dynamic
Static Container	#myreport
Action	Execute JavaScript Code

 

var f01Array = [];  //Array for select records
var f02Array = [];  //Array for deselected records
var l_mode;
if ( $( '#selectunselectall' ).is(':checked') ) { 
  $('input[type=checkbox][name=f01]').each( function( index, listItem ) {
    var mode = $( this ).attr('checked',true);
    f01Array.push($( this ).attr("chkid"));
    l_mode = 'checked';
  });
} else {     
  $('input[type=checkbox][name=f01]').each( function( index, listItem ) {
    var mode = $( this ).attr('checked',false);
    f02Array.push($( this ).attr("chkid"));
    l_mode = 'unchecked';
  });
}
    apex.server.process("SELECT_UNSELECT",{    //Call Ajax Callback process to populate data in Collection
                x01: l_mode,
                f01: f01Array,
                f02: f02Array
    },
    {
        dataType: 'json',
        beforeSend: function() {
        var lSpinner$ = apex.util.showSpinner(); ///displays processing spinners
        },
            success: function(data) {
                 $(".u-Processing").hide();
              if (data.result == 'success') {
                console.log(data.result);
              } else {
                console.log(data.result);
              }           
            }
    });