Preserve Checkbox state while Paginating in Interactive/Classic Report Oracle APEX

 Someone has asked this question to me how can we preserve checked checkboxes in the session state.
This Problem will come when the user checks a few checkboxes and goes to the next set of rows via pagination in the report when the user goes back to the first set of pagination the original checked checkboxes will be no longer checked. this is because the checked checkboxes are not preserved in the session state.

  • Create a collection for preserving the checked checkboxes in the session state using the following code: 
DECLARE
BEGIN
IF APEX_COLLECTION.collection_exists(p_collection_name => 'CHECKBOX') THEN
apex_collection.delete_collection ('CHECKBOX');
END IF;
apex_collection.create_collection_from_query
                   (p_collection_name   => 'CHECKBOX',
                    p_query             => 'select ''N'' c001, EMPNO c001 from EMP',
                    p_generate_md5      => 'YES'
                    );
END;

  • Create an Interactive/classic Report using the following Query and Select the set column attribute Escape special characters “No”. 
  SELECT DISTINCT
            APEX_ITEM.checkbox2 (
               p_idx          => 1,
               p_value        => c002,
               p_attributes   =>    DECODE (ac.c001,
                                            'Y', 'CHECKED',
                                            'UNCHECKED')
                                 || ' onclick = update_checkbox('''
                                 || DECODE (ac.c001, 'Y', 'N', 'Y')
                                 || ''','''
                                 || ac.seq_id
                                 || ''','''
                                 || c002
                                 || '''); ''')
         || APEX_ITEM.hidden (10, ac.seq_id)
            "SELECT",
         empno,
         ename,
         mgr,
         job,
         sal
    FROM emp
         INNER JOIN apex_collections ac
            ON ac.collection_name = 'CHECKBOX' AND ac.c002 = empno
ORDER BY empno DESC
  • Create a function for getting values from the checked checkbox while clicking. Copy and Paste below Jquery Code as per the screenshot. 
function update_checkbox(p_val, p_seq, pPk_val) {
    apex.server.process("CHECKBOX", 
    { 
        x01: p_val,
        x02: p_seq,
        x03: pPk_val
    }, 
    {
        dataType: 'text',
        success: function(pData) {
        console.log('---');
    }
});
}
  • Create an Ajax Call Back Process using the name “CHECKBOX” to save the Checked Checkboxes value in the collection: 
BEGIN
    apex_collection.update_member (
        p_collection_name => 'CHECKBOX',
        p_seq => apex_application.g_x02,
        p_c001 => apex_application.g_x01,
        P_C002 => apex_application.g_x03
    );
exception
    when others then 
    htp.p(dbms_utility.format_error_Backtrace || sqlerrm);
end;


You might Like