Press "Enter" to skip to content

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:

Oracle Apex - Page designer

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

Oracle Apex - Interactive Grid get selected rows

See also:

Have you found the answer to your question? If not, you can discuss it with me in the comments section below or join my Q&A community OrclQA.com for developers and ask your question. It is FREE.

Vinish Kapoor

Follow

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

guest
40 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Madhusudhan Rao
Madhusudhan Rao
6 months ago

Best Blog for Oracle Apex , this works great as expected ….. Thank you

Anver
Anver
5 months ago

Great Information.. But i found an issue in this code.
The current Employee Id shows only the largest Employee Id Selected, rather than the last selected one.

Test Scenario :
Select Employee Id : 107, then select Employee Id 100 or any id less than 107. Current Employee Id will still be 107. It will only change if we select Employee Id greater than 107.

Marc
Marc
5 months ago

Hello,

it works excellent. Just one question. I saved ids of the selected rows to a database and later I would like to preselect rows on my IG on a basis of the saved ids, is it possible something like this?
Thanks

Marc
Marc
Reply to  Vinish Kapoor
5 months ago

Thanks

Raveena
Raveena
5 months ago

Hi,

This works great Thank you

how to capture the selected entire row (EMP ID, FIRST NAME, LAST NAME , etc.) and pass it to the apex collection.

Raveena
Raveena
Reply to  Vinish Kapoor
5 months ago

Thank you for quick response, but here i am facing an issue

the issue is the collection is displaying the old entries as well ( like first time i have selected first two records from EMP table and then next time last two records selected then the collection is displaying the four records rather than the last two recs selected)

Raveena
Raveena
Reply to  Vinish Kapoor
5 months ago

yes i tried by adding plsql Code to truncate the Collection before it go for JS. but with this its only inserting the latest record into collection ( for example if i select rec1 and rec2 then its only inserting rec2 into collection not the both)

Raveena
Raveena
Reply to  Vinish Kapoor
5 months ago

ok thank you 🙂
Basically my action is to redirect to another page after populating the data into collection

George
George
Reply to  Raveena
5 months ago

How do you redirect to another page after populating the collection?
Can I redirect to a modal page?

George
George
Reply to  Vinish Kapoor
5 months ago

I had already done it but the window of the modal page remains in infinite cycle.
If I try to call the modal page from another button it works fine,
But I need to call the modal page from the DA with which I populate the collection

George
George
Reply to  Vinish Kapoor
5 months ago

Sorry, it worked well.

I submitted a single item and that’s why it didn’t work

 Apex.page.submit ({
Request: ‘ANY_REQUEST’,
Set: {‘P29_SELECTED_ROWS’:  SelRecordsJSON},
ShowWait: true});

Raveena
Raveena
Reply to  George
5 months ago

what to pass in Request : ANY_REQUEST?

Raveena
Raveena
Reply to  Vinish Kapoor
5 months ago

Hello vinish, i just added the apex.submit(); and then created a branch to GoToPage . But after submit its not redirecting to target page.

Could you please help me in if i am missing anything.

Thanks

Raveena
Raveena
Reply to  Vinish Kapoor
5 months ago
Request: "",

so here my code would be like below

Request: "Target URL", ?
VARSHA
VARSHA
Reply to  Vinish Kapoor
10 days ago

Hi
Great information.. but how to remove the row from collection when i deselect the row in the interactive grid

VARSHA
VARSHA
Reply to  VARSHA
9 days ago

Any help would be highly appreciated…

George
George
5 months ago

Excellent blog.
If the grid has several pages (1,2, … n) and I have selected rows from more than one page, does the code process the selected rows from all the pages I have visited or only from the page where I am located?

George
George
Reply to  Vinish Kapoor
5 months ago

Ok, thank you

George
George
5 months ago

Hi Vinish,
Is there any way to enable or disable the ability to select rows
depending on the value of some column of the IG?

For example:
In the IG I show service invoices, some owed and others paid,
I would need you to allow me to select only those owed and process them in some way.

George
George
Reply to  Vinish Kapoor
5 months ago

thank you

BRUNO SILVA
BRUNO SILVA
5 months ago

I have a grid where I do data manipulation using a sql pl, when I update the registry I use the command
var model = apex.region (“event”). widget (). interactiveGrid (“getViews”). grid.model;
model.fetchRecords (model._data);
so that the focus remains on the record, however when inserting the record I did not find a way for the focus to fall on the released record, can you help me?

BRUNO SILVA
BRUNO SILVA
Reply to  Vinish Kapoor
5 months ago

exactly. for example, I have a grid with 200 records, divided into 10 pages, suppose the user goes to page 5 and includes a record based on a record on this page, today, I update the grid, automatically the system launches me to the first page of the grid, with that I totally lose the record launched, it is spread out in the middle of the ordering of my grid. What I want is to simply stay on the grid page and focus on that inserted record.

BRUNO SILVA
BRUNO SILVA
Reply to  Vinish Kapoor
5 months ago

right. Directly through the grid it works. Look at the image of my screen format. The grid I only use for navigation of the records. For inclusion, deletion and updating I use a form. When I do an update I use the command mentioned there at the beginning, and it works very well for me, keeping the record updated on the screen, without having to give an update on the complete grid, that is, it only makes me on the record. In the inclusion I have not found a solution, so far I have made a general update on the grid.

BRUNO SILVA
BRUNO SILVA
Reply to  Vinish Kapoor
5 months ago

right. Directly through the grid it works. Look at the image of my screen format. The grid I only use for navigation of the records. For inclusion, deletion and updating I use a form. When I do an update I use the command mentioned there at the beginning, and it works very well for me, keeping the record updated on the screen, without having to give an update on the complete grid, that is, it only makes me on the record. In the inclusion I have not found a solution, so far I have made a general update on the grid.

tela.PNG
BRUNO SILVA
BRUNO SILVA
Reply to  Vinish Kapoor
4 months ago

in my case it didn’t work, but I used the commands

apex.region (“document”). widget (). interactiveGrid (“addFilter”, {
  type: ‘column’,
  columnType: ‘column’,
  columnName: ‘SEQUENTIAL’,
  operator: ‘EQ’,
  value: $ v2 (‘P18_SEQUENCIAL’),
  isCaseSensitive: false
});

to take the inserted record and present

VARSHA
VARSHA
7 days ago

Great work…
1.I tried with adding entire row and pass it to collection..working fine but when i deselect the row it is not refreshing the collection..
i created dynamic action ‘refresh region’ after javascript code.
2.I created ‘ page load’ dynamic action to truncate collection on every page load but it is truncating when i do page load two times.
Any help would be highly appreciated..