How Do I Create a stored procedure for UPDATING a table

 Introduction

Creating a stored procedure for updating a table in Oracle APEX is a smart way to centralize and manage your data modification logic. Instead of writing repetitive SQL update statements across multiple pages or processes, a stored procedure allows you to write the logic once and call it wherever needed. This improves maintainability, security, and consistency throughout your application, especially when updating rows based on parameters passed from the user interface.

How Do I Create a Stored Procedure for Updating a Table in Oracle APEX
Font: Arial | Format: Plain Text

Creating a stored procedure to update a table in Oracle APEX allows you to encapsulate the update logic in one place, making your application easier to manage and reuse. You can call this procedure from forms, buttons, or processes inside your APEX app.

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: Write the Stored Procedure Code
Use the CREATE OR REPLACE PROCEDURE statement to define the logic. Below is an example that updates an employee’s email address based on their employee ID.

CREATE OR REPLACE PROCEDURE update_employee_email (
  p_employee_id IN NUMBER,
  p_new_email   IN VARCHAR2
)
AS
BEGIN
  UPDATE employees
  SET email = p_new_email
  WHERE employee_id = p_employee_id;
END;

In this example:

• The procedure is named update_employee_email.

• It accepts two input parameters: p_employee_id and p_new_email.

• It performs an UPDATE operation on the employees table.

Step 3: Execute the Procedure Code
Click the Run or Execute button to compile the procedure. If there are no errors, the procedure will be created and stored in your schema.

Step 4: Test the Procedure
You can test the procedure by running an anonymous PL/SQL block like this:

BEGIN
  update_employee_email(101, 'newemail@example.com');
END;

This will update the email of the employee with ID 101.

Step 5: Call the Procedure from Your APEX App
In Page Designer, create a button or process that submits data. Under the processing section, add a PL/SQL process with code like this:

BEGIN
  update_employee_email(:P1_EMPLOYEE_ID, :P1_EMAIL);
END;

Make sure that P1_EMPLOYEE_ID and P1_EMAIL are page items that hold the input values.

Step 6: Add Validations if Needed
You can add validations to check if the email is in the correct format or if the employee ID exists before calling the procedure.

Best Practices
Use clear parameter names.
Add error handling using EXCEPTION blocks.
Keep the procedure logic simple and focused.
Grant EXECUTE permission if the procedure needs to be accessed from another schema.

Creating a stored procedure for updating a table helps you manage business logic in one place, improves security, and reduces repeated code across your APEX application. It makes your updates cleaner, safer, and easier to maintain.

Conclusion

Using a stored procedure to update a table simplifies your Oracle APEX application by encapsulating the update logic in a reusable and secure way. It ensures that updates follow a consistent process and allows for easier future changes without touching multiple pages. By calling the procedure from page processes, buttons, or dynamic actions, you maintain clean code separation and gain better control over how and when your data is modified.

Example:

CREATE OR REPLACE PROCEDURE UPDATE customer (   

p_name IN CUSTOMER.NAME%TYPE,  

p_gender IN CUSTOMER.GENDER%TYPE,  

p_address IN CUSTOMER.ADDRESS%TYPE)  

IS  

BEGIN  

UPDATE CUSTOMER SET NAME=p_name, GENDER=p_gender, ADDRESS=p_address WHERE NAME=p_name;  

COMMIT;  

END;