1.Introduction
In Oracle APEX, interactive reports are typically used for reporting purposes, as opposed to interactive grids, which allow for direct modifications to the database. However, there are situations where editing capabilities are needed in interactive reports. In such cases, the apex_item API can be very useful in achieving this functionality.
In this blog, we will learn how to add a custom checkbox for each row in the interactive report conditionally and how to process the checked records.
The following technologies has been used to achieve the same.
- Oracle APEX
- JavaScript
- HTML/CSS
2.Why we need to do
In many business applications, there are scenarios where specific rows in a data table should be protected from further user actions. For example, once an order is processed, it may be “locked” to prevent modifications. In an Oracle APEX Interactive Report, you can achieve this by dynamically enabling or disabling check boxes based on the row’s status, providing a visual cue and functional control to the end users.
3.How do we solve
Step1: I have created a region of type interactive report along with a button named APPLY_CHANGES as like the below.
The following is the source query that used for the above interactive report region.
SELECT
CASE
WHEN EMP_ROLE IN (‘Manager’, ‘Developer’, ‘Engineer’) THEN
apex_item.checkbox(1, EMP_ID)
ELSE
apex_item.checkbox(1, EMP_ID, ‘disabled’)
END AS ch,
EMP_ID,
EMP_NUMBER,
EMP_NAME,
EMP_ROLE,
DOB,
SALARY,
MANAGER,
REMARKS
FROM
EMPLOYEE_MASTER;
From the query you can clearly see that I have used the apex_item.checkbox API to add custom checkbox for each row of the table and also I have enabled the checkbox only for few rows based on the condition. For other records I have disabled the checkbox functionality.
Step 2: Once after the creation of the report region I just changed the heading of column CH into <input type=”checkbox” id=”selectunselectall”>. The basic idea behind this addition is when I click this checkbox which is there in the column heading all the check-boxes that are in the each row with the attribute of disabled is false will also be checked. This kind of feature will be more useful where we need to check and process all the records in the report and the record checking should be happened in a single click.
Code: <input type=”checkbox” id=”selectunselectall”>.
Step 3: Now we are going to add a dynamic action that should be triggered when I check and unchecked the checkbox which is there in the column heading. The intended functionality of the dynamic action will be like when I check the checkbox which is there at the column heading all the other check-boxes that are at the row level should also be checked and the reverse also should be in a similar way.
The below is the code snippet that should be used in the true action (Execute Javascript Code).
if ($(‘#selectunselectall’).is(‘:checked’)) {
console.log(‘checked’);
$(‘input[name=”f01″]:not(:disabled)’).prop(‘checked’, true);
} else {
console.log(‘unchecked’);
$(‘input[name=”f01″]:not(:disabled)’).prop(‘checked’, false);
}
Step 4: Finally I have created a process named “Update Remarks” which will update the remarks column value for each row that are checked.
The below is the PLSQL code snippet that I used in the PL/SQL Code section of the process
DECLARE
v_error_count INTEGER := 0;
BEGIN
FOR i IN 1..apex_application.g_f01.count LOOP
BEGIN
UPDATE EMPLOYEE_MASTER
SET remarks = ‘The remarks is updated at -‘||TO_CHAR(systimestamp,’DD-MON-YYYY HH:MI:SS’)
WHERE emp_id = apex_application.g_f01(i);
EXCEPTION
WHEN OTHERS THEN
v_error_count := v_error_count + 1;
END;
END LOOP;
IF v_error_count > 0 THEN
RAISE_APPLICATION_ERROR(-20001, ‘Error occurred during update’);
END IF;
COMMIT;
END;
And this process is set to fire when the APPLY_CHANGES button is clicked.
4.Conclusion
In summary, the apex_item API provides a flexible way to enhance the functionality of interactive reports in Oracle APEX, allowing for customization’s such as adding check-boxes for record selection and implementing custom processing logic for the selected records.
