Updating a row in a database table is a crucial operation when working with Oracle APEX applications. This can be done using SQL UPDATE statements, PL/SQL processes, Dynamic Actions, or form-based updates. This tutorial will cover different ways to update a row using SQL in APEX.
1. Updating a Row Using SQL Commands
If you have direct access to the SQL Commands interface in Oracle APEX, you can update a row using the UPDATE statement.
Example: Updating Employee Information
UPDATE employees
SET emp_name = 'John Smith', department = 'HR', salary = 6000
WHERE emp_id = 101;
This command updates the name, department, and salary of the employee with emp_id = 101.
Run this query in SQL Commands or any SQL Worksheet.
2. Updating a Row Using a PL/SQL Process in APEX
When working with an APEX Form, you can create a PL/SQL process to update data when the user submits the form.
Steps to Set Up the Form Update Process
Create a Form in APEX
Navigate to App Builder and create a Form on a Table (e.g., employees).
Ensure that page items are created for the columns you want to update (P1_EMP_ID, P1_EMP_NAME, P1_DEPARTMENT, P1_SALARY).
Create a PL/SQL Process
Navigate to Processing > Create a New Process.
Select PL/SQL Process and enter the following code:
BEGIN
UPDATE employees
SET emp_name = :P1_EMP_NAME,
department = :P1_DEPARTMENT,
salary = :P1_SALARY
WHERE emp_id = :P1_EMP_ID;
COMMIT;
END;
This process updates the employee’s name, department, and salary based on the emp_id entered in the form.
Bind variables (:P1_EMP_ID, etc.) fetch values from the form fields.
3. Updating a Row Using a Dynamic Action
You can dynamically update a row when the user clicks a button without submitting the whole page.
Steps to Set Up Dynamic Action for Update
Create a Button (e.g., Update Data)
Go to Dynamic Actions and create a new True Action
Action: Execute PL/SQL Code
PL/SQL Code:
BEGIN
UPDATE employees
SET emp_name = :P1_EMP_NAME,
department = :P1_DEPARTMENT,
salary = :P1_SALARY
WHERE emp_id = :P1_EMP_ID;
COMMIT;
END;
Set Items to Submit
Select P1_EMP_ID, P1_EMP_NAME, P1_DEPARTMENT, P1_SALARY.
These values will be passed into the SQL query.
When the user clicks the Update Data button, the corresponding row is updated without a full page refresh.
4. Updating a Row Using a RESTful API Call
If your APEX application interacts with external systems, you can update data via REST APIs.
Example REST API for Updating Data
PUT /ords/hr/employees/101
{
"emp_name": "Jane Doe",
"department": "Finance",
"salary": 7000
}
This method updates employee 101 through an API call.
5. Updating a Row Using a Custom PL/SQL Procedure
If updates are frequent, it’s good practice to create a PL/SQL procedure for reusability.
Creating a Stored Procedure for Updates
CREATE OR REPLACE PROCEDURE update_employee (
p_emp_id IN NUMBER,
p_emp_name IN VARCHAR2,
p_department IN VARCHAR2,
p_salary IN NUMBER
) AS
BEGIN
UPDATE employees
SET emp_name = p_emp_name,
department = p_department,
salary = p_salary
WHERE emp_id = p_emp_id;
COMMIT;
END update_employee;
/
Calling the Procedure in APEX
BEGIN
update_employee(:P1_EMP_ID, :P1_EMP_NAME, :P1_DEPARTMENT, :P1_SALARY);
END;
This approach allows you to reuse the procedure across multiple pages.
6. Updating Data in an Interactive Grid
If you’re using an Interactive Grid, users can directly edit fields, and APEX handles updates automatically.
Steps to Enable Editing in an Interactive Grid
Create an Interactive Grid based on your table.
Navigate to the Attributes section.
Set Editable to Yes.
Set the Primary Key Column (emp_id).
Save the changes.
Now, users can edit rows directly, and APEX will handle updates automatically.
Best Practices for Updating Rows in APEX
Use Bind Variables (:P1_EMP_ID) to prevent SQL injection.
Validate Input Fields to avoid incorrect or missing data.
Use Commit Statements to ensure updates are saved.
Handle Errors Gracefully by adding exception handling in PL/SQL.
Updating a row using SQL in Oracle APEX can be achieved through multiple methods, including SQL Commands, PL/SQL processes, Dynamic Actions, REST APIs, and Interactive Grids. Each approach depends on your specific use case, whether manual updates, dynamic updates, or automatic updates in grids.
