Implementing Row ID Capture into Page Items in Oracle APEX Interactive Grids

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.

Approach 1: JavaScript for Capturing Selected Row IDs

The first 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.

Step 1: JavaScript Code

The following JavaScript code should be placed in the Execute when Page Loads section of your Oracle APEX page.

Copy Code

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], "EMPNO"));
}

apex.item("P57_IDS").setValue(i_selectID.join(','));

Explanation:

apex.region(“emp”): Refers to the Interactive Grid, where “emp” is the static ID.
getSelectedRecords(): Retrieves the records that have been selected by the user.
i_selectID.push(…): Adds each selected employee ID (EMPNO) to the array.
apex.item(“P57_IDS”).setValue(…): Stores the comma-separated list of IDs in the page item P57_IDS.

Step 2: PL/SQL for Further Processing

Once the IDs are captured into P57_IDS, 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:

Copy Code

DECLARE
    l_ids_string   VARCHAR2(4000) := :P57_IDS;  
    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;

Key Notes:

APEX_STRING.SPLIT: Converts the comma-separated string into an array of IDs.
APEX_COLLECTION: This stores the split IDs for further manipulation in Oracle APEX.

Approach 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.

Implementation Steps:

JavaScript Code to Capture Selected IDs: The following code captures selected EMPNO values from the Interactive Grid:

Enable Pagination: Configure your Interactive Grid to use pagination. This allows users to navigate through extensive datasets in manageable chunks, enhancing usability.

Copy Code

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], "EMPNO") + ",";
}
apex.item("P57_IDS").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.

Copy Code

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.