How do I DELETE manually from a table

 

Introduction
Manually deleting data from a table in Oracle APEX is a common task during development or maintenance when you need to remove specific records based on user input or custom criteria. While APEX provides declarative features for automated deletion in interactive reports and forms, sometimes a manual approach is necessary—especially when implementing custom buttons or writing business-specific logic. Using simple PL/SQL, you can safely control how and when rows are deleted.

To manually delete rows from a table in Oracle APEX, you create a page with controls that allow users to specify which records to delete and then use a PL/SQL process to perform the deletion. This approach gives you full control over when and how rows are removed.

Here’s a detailed step-by-step guide:

Step 1: Identify the Table

Assume you have a table named EMPLOYEES with a primary key column EMP_ID.Step 2: Create a Page with Controls

1. In Oracle APEX, create a new page or use an existing one.

2. Add a text item to capture the record identifier to delete, for example:

   • Page Item: P1_EMP_ID

   • Label: Employee ID to Delete

3. Add a button to trigger the delete action:

   • Name: DELETE_BTN

   • Label: Delete Record

   • Action: Submit Page

Step 3: Add a PL/SQL Process to Delete the Record

1. In the page designer, go to the Processing section.

2. Add a new Process:

   • Name: Delete Employee Record

   • Type: PL/SQL Code

   • Point: After Submit

   • Server-side Condition: When Button Pressed = DELETE_BTN

3. In the PL/SQL Code, write the DELETE statement:

BEGIN
  DELETE FROM EMPLOYEES
  WHERE EMP_ID = :P1_EMP_ID;

  IF SQL%ROWCOUNT = 0 THEN
    APEX_ERROR.ADD_ERROR(
      p_message => 'No record found with Employee ID ' || :P1_EMP_ID,
      p_display_location => apex_error.c_inline_in_notification);
  ELSE
    APEX_UTIL.SET_SESSION_STATE('P1_EMP_ID', NULL);
  END IF;
END;

This code deletes the employee with the specified ID. It also checks if any row was deleted. If none, it shows an inline error message. If successful, it clears the input.

Step 4: Add Confirmation (Optional but Recommended)

To prevent accidental deletes:

• Create a Dynamic Action on the DELETE_BTN button.

• Action: Confirm

• Message: Are you sure you want to delete this record?

Step 5: Run and Test

1. Run the page.

2. Enter an Employee ID in the text box.

3. Click Delete Record.

4. If the record exists, it is deleted; otherwise, an error is shown.

Additional Notes

• Always validate input before running DELETE statements.

• Consider adding authorization checks to restrict who can delete.

• For multiple deletes, you can use interactive grids or reports with row selection and delete actions.

• Use APEX built-in error handling to provide user-friendly feedback.

Manual deletion of rows in Oracle APEX empowers developers to create precise and controlled data removal processes. By capturing user input, confirming actions, and executing PL/SQL DELETE commands, you ensure that data is deleted safely and intentionally. This approach is suitable for custom workflows and scenarios requiring explicit user-driven record removal.

 

EXAMPLE:

In this  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
Deleting records manually from a table in Oracle APEX gives developers precise control over the deletion logic and user interactions. By combining page items, buttons, and PL/SQL processes, you can create secure and targeted delete operations tailored to your application’s needs. Whether triggered by user action or embedded in a custom process, manual deletion provides the flexibility to manage data effectively in any APEX project.