Introduction
Capturing selected row IDs in Oracle APEX Interactive Grids is essential for many dynamic applications where developers must perform batch operations, updates, or any processing based on user-selected records. This post will guide you through two practical approaches to capturing row IDs and storing them in a page item, ready for further processing.
The following technology has been used to achieve the expected output.
- JAVASCRIPT
- Oracle Apex
Why we need to do
This method uses JavaScript to capture the selected row IDs from the Interactive Grid and store them in a hidden page item. This approach is highly responsive, allowing real-time interaction as users select rows.
How do we solve:
Step 1: Approch 1: The following JavaScript code should be placed in the Execute when Page Loads section of your Oracle APEX page.
var grid = apex.region(“emp”).widget().interactiveGrid(“getViews”,”grid”).model;
var selectedRecords = apex.region(“emp”).widget().interactiveGrid(“getSelectedRecords”);
var i_selectID = [];
for (var i = 0; i < selectedRecords.length; i++) {
i_selectID.push(grid.getValue(selectedRecords[i], “emp_no”));
}
apex.item(“P75_id”).setValue(i_selectID.join(‘,’));
- region(“emp”): Refers to the Interactive Grid, where “emp” is the static ID.
- getSelectedRecords(): Retrieves the records that have been selected by the user.
- push(…): Adds each selected employee ID (emp_no) to the array.
- item(“P75_id”).setValue(…): Stores the comma-separated list of IDs in the page item P75_id.
Step 2: PL/SQL for Further Processing
Once the IDs are captured into P75_id, you can process them using PL/SQL. For example, you might want to split the string into individual IDs and store them in an APEX collection:
DECLARE
l_ids_string VARCHAR2(4000) := :P75_id;
l_emp_ids APEX_T_VARCHAR2;
l_emp_id NUMBER;
BEGIN
BEGIN
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(‘EMP_IDS_COLLECTION’);
END;
IF l_ids_string IS NULL OR TRIM(l_ids_string) = ” THEN
RAISE_APPLICATION_ERROR(-20001, ‘No IDs provided.’);
END IF;
l_emp_ids := APEX_STRING.SPLIT(l_ids_string, ‘,’);
FOR i IN 1 .. l_emp_ids.COUNT LOOP
l_emp_id := l_emp_ids(i);
APEX_COLLECTION.ADD_MEMBER(
p_collection_name => ‘EMP_IDS_COLLECTION’,
p_c001 => l_emp_id
);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002, ‘An error occurred: ‘ || SQLERRM);
END;
- SPLIT: Converts the comma-separated string into an array of IDs.
- APEX_COLLECTION: This stores the split IDs for further manipulation in Oracle APEX.
Step 1: Approch 2 : Handling Large Selections with Pagination
When working with large datasets in an Oracle APEX Interactive Grid, managing user selections is crucial for maintaining performance and ensuring a smooth user experience. This approach leverages pagination to limit the number of records users can select at one time, preventing potential performance issues.
1.JavaScript Code to Capture Selected IDs:
- The following code captures selected EMPNO values from the Interactive Grid
2.Enable Pagination:
- Configure your Interactive Grid to use pagination. This allows users to navigate through extensive datasets in manageable chunks, enhancing usability.Paste the JavaScript code snippet to customize the toolbar.
var index,
selectedIds = “,”,
//selectedIds = ” “, You may use any one
selectedId;
model = this.data.model;
for (index = 0; index < this.data.selectedRecords.length; index++) {
selectedIds += model.getValue(this.data.selectedRecords[index], “emp_no”) + “,”;
}
apex.item(“P75_id”).setValue(selectedIds);
Ensure to incorporate logic that alerts users if they attempt to select more than a defined threshold (e.g., 1,000 records), guiding them to make more manageable selections.
Advantages of This Approach:
- Performance Improvement: Limiting the number of selectable records helps maintain application responsiveness.
- User Guidance: Providing feedback on selection limits encourages users to make appropriate choices, enhancing the overall experience.
DECLARE
l_ids_string VARCHAR2(4000) := :P57_IDS;
l_emp_id NUMBER;
l_pos NUMBER := 1;
l_next_pos NUMBER;
BEGIN
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(‘EMP_IDS_COLLECTION’);
IF l_ids_string IS NULL OR TRIM(l_ids_string) = ” THEN
RAISE_APPLICATION_ERROR(-20001, ‘No IDs provided.’);
END IF;
LOOP
l_next_pos := INSTR(l_ids_string, ‘,’, l_pos);
IF l_next_pos = 0 THEN
l_next_pos := LENGTH(l_ids_string) + 1;
END IF;
l_emp_id := TO_NUMBER(TRIM(SUBSTR(l_ids_string, l_pos, l_next_pos – l_pos)));
IF l_emp_id IS NOT NULL AND l_emp_id > 0 THEN
APEX_COLLECTION.ADD_MEMBER(
p_collection_name => ‘EMP_IDS_COLLECTION’,
p_c001 => l_emp_id
);
END IF;
l_pos := l_next_pos + 1;
IF l_pos > LENGTH(l_ids_string) THEN
EXIT;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20003, ‘An error occurred: ‘ || SQLERRM);
END;
Conclusion:
- Capturing and processing selected row IDs in Oracle APEX Interactive Grids can be achieved efficiently using both client-side (JavaScript) and server-side (PL/SQL) techniques. Depending on your application requirements, you can choose to either capture and store IDs using JavaScript for immediate feedback or handle everything with PL/SQL for centralized control.
- Both approaches offer flexibility for developers to create robust, interactive, and dynamic web applications. With the use of Oracle APEX’s powerful tools like APEX_COLLECTION and APEX_STRING.SPLIT, developers can seamlessly manage data selected by users in the Interactive Grid.
