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: