Save Selected Interactive Grid Records into a Collection - Oracle APEX

Here I am giving an example to save selected interactive grid records into a oracle apex collection.

Step 1: Create a new blank page.

Note: Mine was page 20. You will need to update reference to "P20" with your page number if it's different.

Step 2: Create a new interactive grid report region to the page using below query. Set Static Id "EmpDetails" to the region.

SELECT * 

  FROM (SELECT emp.empno,

               emp.ename,

               emp.JOB,

               dept.dname department,

               dept.loc LOCATION,

               mgr.ename manager,

               emp.hiredate,

               nvl(emp.sal,0) salary,

               nvl(emp.comm,0) commission

          FROM eba_demo_chart_emp emp,

               eba_demo_chart_dept dept,

               eba_demo_chart_emp mgr

         WHERE emp.deptno = dept.deptno

           AND emp.mgr      = mgr.empno (+)

         ORDER BY emp.ename ASC

        );

Step 3: Create a new field to the IG report. Set name to APEX$ROW_SELECTOR and change the type to Row Selector.

Step 4: Set field "EMPNO" as primary key to the report.

Step 4: Create a new item to the region. Set name to P20_SELECTED_ROWS and change the type to Hidden.

Step 5: Create a new button to the region. Set name to MOVE_INTO_COLLECTION and change the behavior (action) to Defined by Dynamic Action.

Step 6: Create a new dynamic action that fires when the button is clicked. The easiest way to do this is to right click the button and select Create Dynamic Action Set name to Get Selected Values IG.

Step 7: Set action to Execute JavaScript Code and copy and paste the following code into the JavaScript Code section.

$s("P20_SELECTED_ROWS", "");

// IG static id is defined as emp

var gridView = apex.region("EmpDetails").widget().interactiveGrid("getViews").grid;

var records = gridView.getSelectedRecords();

var EmpNo;

// define empty JSON object to hold selected rows

var selRecords = {

 "rows": []

};

var selRecordsJSON;

if (records.length > 0) {

 if (confirm("Do you want to move selected record(s) into collection? Please confirm.")) 

    {

  $.each(records, function(i, r) {

   // employee number defined as PK for IG

   EmpNo = gridView.model.getRecordId(r);

   // you can also get other columns values using below code

   // ENAME is column name in IG query

   //empName = gridView.model.getValue(r, "ENAME");

   // Push selected rows into an array

   selRecords.rows.push({

    "EmpNo": EmpNo

   });

  });

  // convert JSON into string

  selRecordsJSON = JSON.stringify(selRecords);

  console.log(selRecordsJSON);

  apex.page.submit({

   request: "MOVE_INTO_COLLECTION",

   set: {

    "P20_SELECTED_ROWS": selRecordsJSON

   },

   showWait: true

  });

 }

} else {

alert ("Please select atleast one line.");

}

Steps to Create Dynamic Action:

Event: Click

Selection Type: Button

Button: MOVE_INTO_COLLECTION

True Action: 

Execute JavaScript Code:

<<JS Code - Ref Sep 7>>

Note: When the dynamic action is success, apex.submit calls the button (MOVE_INTO_COLLECTION) processes. 

Step 8: Create a new plsql process that fires when the button is clicked. The easiest way to do this is, Go to processes and right click the processes and create process. Set name to Load Selected Records into a Collection and copy and paste the following code into the plsql code section.

DECLARE

  l_emp_no         NUMBER;

  l_values         apex_json.t_values;

  l_row_count      pls_integer;

  l_emp_collection VARCHAR2(100) := 'EMPLOYEE_DETAILS_COLLECTION';

BEGIN

  IF apex_collection.collection_exists (p_collection_name => l_emp_collection) THEN

     apex_collection.delete_collection (p_collection_name => l_emp_collection);

  END IF;

  apex_collection.create_or_truncate_collection (p_collection_name => l_emp_collection);

  -- parse text to JSON

  apex_json.parse(p_values => l_values, 

                  p_source => :p20_selected_rows);

  -- get number of rows selected

  l_row_count := apex_json.get_count(p_path => 'rows', 

                                     p_values => l_values);

  -- loop through the selected rows

  FOR i IN 1..l_row_count

  loop

    -- get Primary Key value from JSON data

    l_emp_no := NULL;

    l_emp_no := to_number(apex_json.get_varchar2(p_path => 'rows[%d].EmpNo', 

                                                 p0 => i, p_values => l_values

                                                )

                          );

    -- load emp no into collection

    apex_collection.add_member (p_collection_name => l_emp_collection, 

                                p_n001 => l_emp_no,

                                p_n002 => l_row_count

                               );

  END loop;

END;

Note: You will have the output in collection now.

How to check output:

Option 1: Create classical report region to the page using below query.

SELECT n001 AS empno

  FROM apex_collections

 WHERE collection_name = 'EMPLOYEE_DETAILS_COLLECTION';

Result: (Selected records loaded into collection)

Option 2: As soon as you click on Session in developer toolbar, you will be navigating to session state info page, there you should change option view to Collections then you can able to view all collections records.

I hope there is enough information here for you to understand what's going on. Let me know if you have any questions. The demo is here

That's it.

Happy APEXing!!!...

References/Credits:

• Interactive Grid - Bulk Operation on Selected Rows