How to Select All Records in Interactive Report Without Any Limitation

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.

Create a table which has large no of records i.e. INDIA_PINCODE.
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;

Create a Interactive report on above mentioned table and give static_id “myreport”

Copy Code

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 (+)

Change Heading of Select Column and paste below code

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

Create a Ajax Callback process named SELECT_UNSELECT

Copy Code

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;

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

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

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

You might like