Introduction
In Oracle APEX, deleting records manually from a table is a common task when managing application data. Whether you're building an admin dashboard or managing transactional data, it's often necessary to provide users with the ability to remove specific records directly from the interface. By combining form controls, SQL commands, and APEX processes, you can set up a secure and functional way to perform manual deletions from a table. This blog post explains the detailed steps to accomplish this, including how to use buttons, PL/SQL processes, and appropriate validations to safely delete rows.
In Oracle APEX, manually deleting records from a table involves creating a user interface element, such as a button or a link, that triggers a process to remove the selected row from the database. This task requires combining declarative page components with PL/SQL code to execute the DELETE statement securely and efficiently.
To begin, identify the table and the primary key column that uniquely identifies each row. This primary key will be used to specify which record to delete. Typically, you will display the table data in a Classic Report or Interactive Grid, and for each row, provide a Delete button or link.
Next, create a button or a link in the report that passes the primary key value to the page or process. For example, you can add a column with a link labeled "Delete" that includes the primary key as a URL parameter. Alternatively, in Interactive Grids, you can enable row selection and add a button outside the grid for deletion.
After setting up the interface, create a PL/SQL process that runs when the delete action is invoked. This process will use the passed primary key value to execute a DELETE statement on the target table. Here is a basic example of such a process:
BEGIN
DELETE FROM your_table
WHERE primary_key_column = :P1_PRIMARY_KEY;
COMMIT;
END;
Ensure that the process includes proper error handling and commit the transaction to make the deletion permanent. You may also want to add a confirmation dialog before deletion to prevent accidental data loss.
Finally, refresh the report or region displaying the data after the deletion, so the user sees the updated state of the table without the deleted record. This can be done using Dynamic Actions that refresh the report region upon completion of the delete process.
By following these steps, you can implement manual deletion functionality in Oracle APEX that is both user-friendly and secure, maintaining data integrity and providing a seamless user experience.
EXAMPLE:
In this example we will select a value with the dropdown list, display the ID in a textbox, and delete it from the database via a manual button push, with a report display.
Step 1 Create a dropdown list with the following values
Step 2 – Add a dynamic Action and two True branches
Step 3 - In the first True branch
Here is the code used.
var DisplayValueVar =$("#P50_TESTA_DROPDOWN option:selected").val();
console.log(DisplayValueVar);
apex.item('P50_TESTA_ID_VALUE').setValue(DisplayValueVar);
var DisplayTextVar =$("#P50_TESTA_DROPDOWN option:selected").text();
console.log(DisplayTextVar);
apex.item('P50_NEW_TEXT').setValue(DisplayTextVar);
Step 4 – In the second true branch
Step 5 – Add the button
Step 6- Add the delete execute true branch with the following settings
Here is the code that we used
BEGIN
DELETE FROM TEST_A
WHERE ID = :P50_TESTA_ID_VALUE;
END;
Step 7 – Add the report to the application
Step 8 – In the button’s second True branch set as follows.
You should see something like this
Conclusion
Manually deleting rows from a table in Oracle APEX allows developers to create powerful, flexible interfaces tailored to user needs. By setting up the proper form controls and backend logic, you ensure that data removal is handled accurately and securely. Leveraging APEX’s declarative framework along with custom PL/SQL code makes it simple to implement deletion functionality that aligns with your application's workflow and data integrity requirements.
