Oracle Apex: Interactive Grid Get Selected Rows Example

This Oracle Apex tutorial shows you how to get selected rows of the interactive grid in Oracle Apex.

To demonstrate this example in Oracle Apex, I will create a blank page and then will add the following regions:

Employees (Interactive Grid)
Selection (Interactive Report)
Current Selection (Static Content)

The following EMPLOYEES table used in this tutorial, you can create it in your schema and insert some data to practice this example:

CREATE TABLE  "EMPLOYEES" 
   (	"EMPLOYEE_ID" NUMBER(6,0), 
	"FIRST_NAME" VARCHAR2(20), 
	"LAST_NAME" VARCHAR2(25), 
	"EMAIL" VARCHAR2(25), 
	"PHONE_NUMBER" VARCHAR2(20), 
	"HIRE_DATE" DATE, 
	"JOB_ID" VARCHAR2(10), 
	"SALARY" NUMBER(8,2), 
	"COMMISSION_PCT" NUMBER(2,2), 
	"MANAGER_ID" NUMBER(6,0), 
	"DEPARTMENT_ID" NUMBER(4,0)
   )
/

Step-1 Create an Interactive Grid Region

Title: Employees
Type: Interactive Grid
SQL Query:

Add the following SQL query in it:

select EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       EMAIL,
       PHONE_NUMBER,
       HIRE_DATE,
       JOB_ID,
       SALARY,
       COMMISSION_PCT,
       MANAGER_ID,
       DEPARTMENT_ID
  from EMPLOYEES;

Step-2 Create a Hidden Page Item for Region Employees created above.

Name: P9_EMPIDS
Type: Hidden
Value Protected: No

Step-3 Create a Region for Interactive Report

This report is to show the selected employee ids:

Title: Selection
Type: Interactive Report
Location: Local Database
Type: SQL Query (Enter the following SQL query in it)

SELECT t.Column_Value AS employee_id
      FROM TABLE(Apex_String.Split(RTRIM(LTRIM(:P9_EMPIDS, ':'), ':'),
                                   ':')) t

Page Items to Submit: P9_EMPIDS

Step-4 Create a Static Region to Show Last Selected Employee ID

This region is to show the most current (last) selected employee id.

Title: Current Selection
Type: Static Content

Step-5 Create a Page Item in the above Region (Current Selection)

Name: P9_CURRENT_EMPID
Type: Text Field
Label: Last Selected Employee ID

Step-6 Create a Dynamic Action for the Region Employees to get selected rows

Create a dynamic action for the region Employees created in step-1.

Name: da_select
Event: Selection Change [Interactive Grid]
Selection Type: Region
Region: Employees

True Action:

Action: Execute JavaScript Code
Code: Enter the following JavaScript Code:

var i, i_empids = ":", i_empid,

model = this.data.model;

for ( i = 0; i < this.data.selectedRecords.length; i++ ) {
    
    i_empid = model.getValue( this.data.selectedRecords[i], "EMPLOYEE_ID");
    
    i_empids += model.getValue( this.data.selectedRecords[i], "EMPLOYEE_ID") + ":";
    
}

apex.item( "P9_EMPIDS" ).setValue (i_empids);
apex.item( "P9_CURRENT_EMPID" ).setValue (i_empid);

The above JavaScript code will set the value for P9_EMPIDS in this format (:101:102:106:103:) for multiple employee ids. And will set the single employee id for the item P9_CURRENT_EMPID.

Step-7 Create another True action in the above dynamic action (da_select) as follows:

Action: Refresh
Selection Type: Region
Region: Selection (created in step-3)

Finally, you will have the regions and page items as shown in the following screenshot:

Now save the changes and run the page. You will have the output as shown in the below image: