Summing Column Values in Interactive Grids with PL/SQL

Introduction:

Interactive Grids (IG) in Oracle APEX offer a powerful and versatile way to interact with data. Sometimes, business requirements demand dynamic calculations, such as summing up the values of a specific column in the grid. This blog explains how to achieve this functionality by leveraging PL/SQL, making it easy to display the calculated sum in a page item dynamically.

The following technologies has been used to achieve the same.

Oracle APEX
Java Script

Why We Need to Do This?

1. Dynamic Calculations for Real-Time Analysis

Business applications often require users to modify data in grids and instantly see the impact of these changes. Calculating the sum of a column dynamically ensures real-time insights without needing manual intervention.

2. Accuracy and Automation

Manual calculations or static approaches are prone to human error. By automating this process using PL/SQL, you ensure that the calculations are accurate, consistent, and performed without user effort.

3. Enhanced User Experience

Displaying the total value dynamically in a designated field improves the usability of the application, making it more interactive and user-friendly. It also supports decision-making by providing instant feedback on data changes.

4. Seamless Integration with Business Logic

Dynamic summation is often a prerequisite for other business processes, such as validations, conditional actions, or report generation. Automating this task facilitates smoother workflows.

How Do We Solve It?

Below are the step-by-step instructions to implement this feature in Oracle APEX.

Step 1: Create a Dynamic Action for IG-Change

1. Navigate to the Interactive Grid where you want to perform the summation.
2. In the page designer, create a new Dynamic Action:
   1. Event: Selection Change [Interactive Grid]
   2. Selection Type: Region
   3. Region: Select your Interactive Grid.

Add a True Action:
Action: Execute PL/SQL Code

        

Step 2: Add the PL/SQL Code

Inside the PL/SQL Action, paste the following code:

DECLARE
    l_region_id NUMBER;
    l_context   apex_exec.t_context;
    l_sal_ids   NUMBER;
    l_sal_sum   NUMBER := 0;  
BEGIN
    — Retrieve the region ID for the given application, page, and static ID
    SELECT region_id
      INTO l_region_id
      FROM apex_application_page_regions
     WHERE application_id = 149659  — Updated Application ID
       AND page_id        = 5       — Updated Page Number
       AND static_id      = ‘myintgr’;  — Updated Static ID

    — Open the query context for the specified region
    l_context := apex_region.open_query_context (
                        p_page_id => 5,             — Updated Page Number
                        p_region_id => l_region_id );

    — Get the column position of the ‘SAL’ column
    l_sal_ids := apex_exec.get_column_position( l_context, ‘SAL’ );

    — Loop through the rows to calculate the sum of the ‘SAL’ column
    WHILE apex_exec.next_row( l_context ) LOOP
        l_sal_sum := l_sal_sum + apex_exec.get_number( l_context, l_sal_ids );
    END LOOP;
    
    — Assign the total sum to the page item
    :P5_TOTAL_VALUE := l_sal_sum;  — Updated Page Item
    
    — Close the context after processing
    apex_exec.close( l_context );
EXCEPTION
    WHEN others THEN        
        — Close the context in case of an exception
        apex_exec.close( l_context );
        RAISE;
END;

Step 3: Configure the Items to Return

1. In the “Items to Return” field of the PL/SQL Action, specify the page item where the total sum will be displayed, e.g., P5_TOTAL_VALUE.
2. Ensure that this page item is present on the page and set to Display Only.

Step 4: Test the Functionality

1. Save and run the page.
2. Modify values in the “SAL” column of the Interactive Grid.
3. Observe that the total sum is dynamically calculated and displayed in the designated page item (P5_TOTAL_VALUE).

Conclusion:

Summing column values dynamically in Interactive Grids with PL/SQL provides a seamless way to handle calculations. This approach ensures flexibility and accuracy, enhancing both the developer’s and user’s experience. By following the above steps, you can efficiently implement this functionality in your Oracle APEX applications.