How Do I Create a Stored procedure for DELETE and execute the procedure

 Introduction

Creating a stored procedure for deleting data from a table in Oracle APEX is a secure and efficient way to manage deletion logic. Instead of writing DELETE statements directly on multiple pages, a stored procedure allows you to centralize and control how records are removed from the database. This is especially useful when you want to apply conditions, logging, or validation before deleting data. With the procedure in place, you can execute it easily from buttons, processes, or PL/SQL blocks within your APEX application.

Creating a stored procedure to delete a record from a table in Oracle APEX is a safe and structured way to manage data removal. Instead of writing DELETE statements directly inside page processes or buttons, the logic is stored in the database, making it reusable, secure, and easier to maintain.

Step 1: Open SQL Workshop
Log in to Oracle APEX. From the main menu, go to SQL Workshop and click on SQL Commands.

Step 2: Create the Stored Procedure
Write a PL/SQL procedure using the CREATE OR REPLACE PROCEDURE syntax. Below is a basic example for deleting an employee by ID:

CREATE OR REPLACE PROCEDURE delete_employee (
  p_employee_id IN NUMBER
)
AS
BEGIN
  DELETE FROM employees
  WHERE employee_id = p_employee_id;
END;

Explanation:

• The procedure is named delete_employee.

• It accepts one input parameter: p_employee_id.

• The procedure performs a DELETE operation based on the employee ID.

Step 3: Execute the Procedure to Test It
You can test the procedure using an anonymous PL/SQL block in SQL Commands:

BEGIN
  delete_employee(101);
END;

This will delete the row where employee_id = 101, assuming the record exists.

Step 4: Use the Procedure in Your APEX Application
To call the procedure from a button or process in your APEX page:

1. In Page Designer, create a button (for example, "Delete").

2. Under the Processing section, create a new PL/SQL process.

3. Set the execution point to After Submit or when the button is clicked.

4. Use the following code in the process:

BEGIN
  delete_employee(:P1_EMPLOYEE_ID);
END;

Make sure that P1_EMPLOYEE_ID is a page item holding the employee ID you want to delete.

Step 5: Add Success Message and Validation (Optional)
You can display a success message by setting the process message property or add a validation process to confirm that the ID exists before deletion.

Best Practices

• Always validate the input parameter to avoid accidental deletions.

• Include exception handling inside the procedure using EXCEPTION WHEN OTHERS THEN.

• Use audit logging if you need to track deleted records.

• Grant EXECUTE privilege if the procedure is used by another schema.

By using a stored procedure for deleting records, you gain centralized control over data removal logic, reduce duplication across your application, and ensure that deletions follow consistent rules. This method enhances security, reusability, and code clarity in Oracle APEX.

Conclusion
Using a stored procedure for deleting records gives you better control, reusability, and maintainability of your data operations in Oracle APEX. It ensures that deletion follows consistent business rules and reduces the chance of accidental data loss. Executing the procedure from your application is straightforward, and it also allows you to incorporate exception handling and validation logic as needed. This approach improves both the reliability and security of your APEX solutions.