This article explores a solution for managing all records in an Interactive Grid, whether filtered or not, and inserting/updating them into APEX collections for subsequent processing. In contrast to a previous blog that focused on handling only selected rows, this article extends the approach to include all record states, providing a more comprehensive data management solution.
Use Cases
- Processing All Records After Filtering
- Scenario: Users apply filters to the Interactive Grid and want to process all records that match the filter criteria.
- Solution: The provided JavaScript function captures all records currently visible in the grid (after applying filters) and sends them to a server-side process. This ensures that any data visible in the grid, regardless of whether it’s filtered or not, is included in the processing.
- Bulk Data Processing
- Scenario: Users need to push all records from the grid to another system or table, regardless of any applied filters.
- Solution: The JavaScript function collects all records from the grid and formats them into JSON. This JSON is then sent to a PL/SQL process, which handles inserting the records into an APEX collection. This approach is useful for bulk operations where all records need to be processed or transferred.
- Data Export
- Scenario: Users want to export all records from the Interactive Grid to an external system or file format for reporting or integration purposes.
- Solution: By sending all records as JSON to a server-side process, you can facilitate data export in various formats. This data can then be converted or used for external reporting or integration needs.
- Data Synchronization
- Scenario: The application requires synchronization of all visible data with a central database or external system.
- Solution: The JavaScript function gathers all records from the grid and sends them to the server. The server-side PL/SQL process can then handle synchronization tasks, ensuring that all visible data is up-to-date across systems.
JavaScript Function: Collecting and Sending All Records
The following JavaScript function collects all records from the Interactive Grid, regardless of any filters applied, and sends them to a server-side PL/SQL process.
function selectRecords() {
let str = '{ "data" : [',
// Retrieve the grid and model from the interactive grid region
grid = apex.region("ig_emp_id").widget().interactiveGrid("getViews", "grid"),
model = grid.model;
// Iterate through each selected record
model.forEach(function(records, index, id) {
var vEmpID = model.getValue(records, "EMPNO"), // Updated field name to EMPID
vEName = model.getValue(records, "ENAME"),
vDeptNo = model.getValue(records, "DEPTNO"),
vHireDate = model.getValue(records, "HIREDATE"),
meta = model.getRecordMetadata(id);
// Construct the JSON string for the selected records
//if (meta.updated || meta.deleted) {
str = str + '{ "EMPNO" :"'+vEmpID+'","ENAME":"'+vEName+'","DEPTNO":"'+vDeptNo+'","HIREDATE":"'+vHireDate+'" },';
// }
});
// Finalize the JSON string
str = str + ']}';
str = str.replace(',]', ']'); // Remove trailing comma
console.log("str " + str);
// Send the JSON data to the server process
apex.server.process(
'SELECT_RECORDS', {
x01: str
}, {
dataType: 'JSON',
success: function(data) {
let vErrorCode = data.return_status,
vErrorMessage = data.return_message;
if (vErrorCode == "S") {
// Handle error case
console.log(data.return_message);
apex.message.showPageSuccess(data.return_message);
} else {
// Handle success case
alert("ERROR");
// Uncomment the following line if you want to submit a page or process
// apex.submit("SUBMIT");
}
},
}
);
}
PL/SQL Process: Handling and Inserting Records
The PL/SQL block processes the JSON data sent from the JavaScript function, inserting records into an APEX collection.
DECLARE
l_emp_coll apex_collections.collection_name%TYPE := 'EMP_COLL'; -- Collection name to match JavaScript
BEGIN
-- Create or truncate the APEX collection
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(p_collection_name => l_emp_coll );
-- Iterate over the JSON data and add members to the collection
FOR rec IN (
SELECT empid, -- Primary Key
ename,
deptno,
hiredate
FROM JSON_TABLE(
apex_application.g_x01, -- JSON data received from JavaScript
'$.data[*]' -- Path to the array of records in the JSON
COLUMNS (
empid NUMBER PATH '$.EMPNO', -- Column names to match JSON structure
ename VARCHAR2(250) PATH '$.ENAME',
deptno NUMBER PATH '$.DEPTNO',
hiredate VARCHAR2(250) PATH '$.HIREDATE'
)
)
)
LOOP
-- Add each record to the APEX collection
APEX_COLLECTION.ADD_MEMBER(
p_collection_name => l_emp_coll ,
p_n001 => rec.empid, -- Use primary key as the unique identifier
p_c001 => rec.ename,
p_c002 => rec.deptno,
p_c003 => rec.hiredate
);
END LOOP;
-- Return a JSON response indicating success
apex_json.open_object;
apex_json.write('return_status', 'S'); -- 'S' for Success
apex_json.write('return_message', 'Records successfully processed.');
apex_json.close_object;
EXCEPTION
WHEN OTHERS THEN
-- Handle exceptions and return error details
apex_json.open_object;
apex_json.write('return_status', 'E'); -- 'E' for Error
apex_json.write('return_message', 'Error occurred: ' || SQLERRM);
apex_json.close_object;
END;
Triggering the JavaScript Function
To execute the selectRecords() function, create a button in your APEX application and configure it to run the JavaScript code:
- Add a Button: Place a new button on the page containing the Interactive Grid.
- Configure Dynamic Action: Set the button’s action to execute JavaScript code.
- Add JavaScript Code: In the button’s properties, enter the following code to call the
selectRecords()function:
selectRecords();
or pass as url
javascript:selectRecords();By following these steps, you ensure that the button triggers the JavaScript function, collects all records from the grid, and sends them to the server for processing.
Conclusion
This approach allows Oracle APEX developers to efficiently manage and process all records in an Interactive Grid. Whether handling filtered data, performing bulk operations, or integrating with external systems, this method provides a robust solution for comprehensive data processing and management in APEX applications.
