Updating a row in Oracle APEX can be done efficiently using SQL statements combined with replacement strings. Replacement strings allow you to dynamically insert values into SQL queries using APEX session variables, making them useful for personalization and multi-user applications.
This tutorial explains how to update a row using SQL and replacement strings in APEX, along with practical examples.
1. Understanding Replacement Strings in APEX
Replacement strings in Oracle APEX are session variables that store dynamic values. These can be used in SQL queries, PL/SQL blocks, and HTML regions.
Commonly Used Replacement Strings
&APP_USER. → Retrieves the logged-in user.
&APP_ID. → Stores the application ID.
&SESSION. → Stores the session ID.
&P1_EMP_ID. → Refers to a page item (e.g., Employee ID field).
In SQL, replacement strings are used in the format &VARIABLE_NAME. (with a trailing dot).
2. Using SQL to Update a Row with Replacement Strings
When using replacement strings in a SQL UPDATE statement, you can substitute page items dynamically.
Example: Updating Employee Information Using a Replacement String
UPDATE employees
SET emp_name = '&P1_EMP_NAME.',
department = '&P1_DEPARTMENT.',
salary = &P1_SALARY.
WHERE emp_id = &P1_EMP_ID.;
Explanation
&P1_EMP_NAME. → Replaces with the value entered in the P1_EMP_NAME text field.
&P1_DEPARTMENT. → Replaces with the value from P1_DEPARTMENT.
&P1_SALARY. → Replaces with the salary value.
&P1_EMP_ID. → Identifies which employee row to update.
This query can be run in SQL Commands or within a process in APEX.
3. Using a PL/SQL Process with Replacement Strings
For a more interactive approach, you can create a PL/SQL Process in APEX that updates a row when a form is submitted.
Steps to Create a PL/SQL Process in APEX
Create an APEX Form on the employees table.
Ensure that page items exist:
P1_EMP_ID
P1_EMP_NAME
P1_DEPARTMENT
P1_SALARY
Navigate to Processing > Create 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;
Save and Run the page.
When the user submits the form, the record updates dynamically using replacement string values from the APEX session.
4. Using Dynamic Actions to Update a Row with Replacement Strings
Instead of a full-page submit, you can use Dynamic Actions to update a row without reloading the page.
Steps to Set Up a Dynamic Action for Update
Create a Button (e.g., Update Employee)
Go to Dynamic Actions
Click Create Dynamic Action and select:
Event: Click
Selection Type: Button
Button Name: Update Employee
Click Create 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
Click Save and Run
When the Update Employee button is clicked, the row updates without refreshing the page.
5. Updating a Row Using an APEX Collection and Replacement Strings
If you need to update multiple rows dynamically, an APEX Collection can store temporary data before updating the table.
Steps to Use an APEX Collection for Updates
Create a Collection and add a record:
BEGIN
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION('EMP_UPDATE');
APEX_COLLECTION.ADD_MEMBER(
p_collection_name => 'EMP_UPDATE',
p_c001 => '&P1_EMP_ID.',
p_c002 => '&P1_EMP_NAME.',
p_c003 => '&P1_DEPARTMENT.',
p_c004 => &P1_SALARY.
);
END;
Update the Table Using the Collection
BEGIN
FOR rec IN (SELECT c001 emp_id, c002 emp_name, c003 department, c004 salary
FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = 'EMP_UPDATE')
LOOP
UPDATE employees
SET emp_name = rec.emp_name,
department = rec.department,
salary = rec.salary
WHERE emp_id = rec.emp_id;
END LOOP;
COMMIT;
END;
This method is useful when dealing with batch updates in APEX.
6. Best Practices for Using Replacement Strings in SQL Updates
Use Bind Variables (:P1_EMP_NAME) Instead of Replacement Strings for security and better performance.
Validate User Input before updating the database to prevent errors.
Avoid Using Replacement Strings in PL/SQL Blocks if possible, as bind variables are more secure.
Use Commit Statements Carefully to avoid accidental data loss.
Debug Using APEX Session State to check if values are being passed correctly.
Updating a row in APEX using SQL and replacement strings provides a dynamic way to modify data. Whether using SQL Commands, PL/SQL Processes, Dynamic Actions, or APEX Collections, replacement strings allow for flexible updates. However, for better security and performance, bind variables are generally preferred in PL/SQL.
