Deleting a row from a database table in Oracle APEX can be done in multiple ways, depending on how the user interacts with the application. This tutorial covers different methods to delete a row using SQL, including SQL commands, PL/SQL processes, dynamic actions, and interactive grid reports.
1. Deleting a Row Using a SQL DELETE Statement
The simplest way to delete a row is to use a SQL DELETE statement.
Basic SQL DELETE Syntax
DELETE FROM employees WHERE emp_id = 101;
This statement removes the row where emp_id is 101 from the employees table.
2. Creating a DELETE Process in APEX Using a Page Item
In a form-based APEX application, a user selects a record, and a button triggers the deletion process.
Steps to Create a Delete Process in APEX
Create a Form Page based on the table you want to modify.
Ensure there is a page item (e.g., P1_EMP_ID) to store the row ID.
Add a Delete button.
Go to Processing and click Create Process.
Choose PL/SQL Process and enter the following code:
BEGIN
DELETE FROM employees WHERE emp_id = :P1_EMP_ID;
COMMIT;
END;
Set Condition to When Button Pressed → Select the Delete button.
Save and run the page.
Now, when a user selects a record and clicks the Delete button, the row is removed from the table.
3. Deleting a Row from an Interactive Grid
Interactive Grids allow users to manage records, including deleting rows directly.
Steps to Enable Deletion in an Interactive Grid
Create an Interactive Grid based on a table.
Select the grid region and go to Attributes.
In the Edit section, enable Allow Row Deletion.
A delete icon appears in the grid. Users can select rows and delete them.
Ensure that Automatic Row Processing (DML) is enabled under Processing.
Now users can delete rows directly from the Interactive Grid.
4. Using a Dynamic Action to Delete a Row Without Reloading the Page
Dynamic Actions allow deleting records without a full-page refresh.
Steps to Create a Dynamic Action for Deletion
Create a Delete Button on the page.
Go to Dynamic Actions and click Create Dynamic Action.
Set Event to Click and Selection Type to Button.
Add a True Action → Execute PL/SQL Code and enter:
BEGIN
DELETE FROM employees WHERE emp_id = :P1_EMP_ID;
COMMIT;
END;
Under Settings, set Items to Submit → P1_EMP_ID.
Add another True Action → Refresh → Select the report region.
Save and test.
When the Delete button is clicked, the row is removed, and the report refreshes automatically.
5. Deleting a Row Using SQL and APEX_COLLECTIONS
APEX Collections allow temporary data storage and batch deletions.
Steps to Delete Using APEX Collections
Store Data in an APEX Collection
BEGIN
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION('EMP_DELETE');
APEX_COLLECTION.ADD_MEMBER(p_collection_name => 'EMP_DELETE', p_c001 => :P1_EMP_ID);
END;
Process the Deletion
BEGIN
FOR rec IN (SELECT c001 emp_id FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = 'EMP_DELETE')
LOOP
DELETE FROM employees WHERE emp_id = rec.emp_id;
END LOOP;
COMMIT;
END;
This approach is useful for managing deletions across multiple sessions before finalizing the database update.
6. Using a Confirmation Message Before Deleting
To prevent accidental deletions, a confirmation message can be added before executing the delete action.
Steps to Add Confirmation to a Delete Button
Select the Delete button in APEX.
Go to Behavior and find Confirmation.
Enter a message such as:
"Are you sure you want to delete this record?"
Save and test.
Now, when a user clicks Delete, a pop-up asks for confirmation before executing the delete process.
7. Best Practices for Deleting Rows in APEX
Always use a confirmation message before deleting records.
Use bind variables (:P1_EMP_ID) instead of replacement strings (&P1_EMP_ID.) to prevent SQL injection.
Ensure proper user authorization before allowing delete actions.
Consider using soft deletes by marking records as inactive instead of permanently deleting them.
Always test in a development environment before deploying changes.
Oracle APEX provides multiple ways to delete a row using SQL, including form-based deletion, dynamic actions, interactive grids, and APEX collections. Each method has its advantages depending on the application requirements. By implementing best practices like using confirmation messages, securing inputs, and avoiding accidental deletions, you can ensure a smooth and user-friendly experience.
