How to Get Selected Rows from an Interactive Grid in Oracle Apex

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: