Adding a row to a database table is a common requirement when working with Oracle APEX applications. This can be achieved using SQL INSERT statements in different ways, such as through a SQL command, PL/SQL process, Dynamic Actions, or Form Submissions. This tutorial covers multiple approaches to inserting a row using SQL in APEX.
Using SQL to Add a Row in APEX
1. Using SQL Commands to Insert Data
If you have direct access to the SQL Commands interface in Oracle APEX, you can use a simple INSERT statement to add a row to a table.
Example: Inserting Data into an Employees Table
INSERT INTO employees (emp_id, emp_name, department, salary)
VALUES (101, 'John Doe', 'IT', 5000);
This command adds a new employee with ID 101, name 'John Doe', department 'IT', and salary 5000.
Run this query in SQL Commands or any SQL Worksheet.
2. Using a PL/SQL Process in APEX to Insert Data
When using an APEX form, you can create a PL/SQL process to insert data when a user submits the form.
Steps to Insert a Row Using a Form
Create a New Form
In APEX, create a Form page based on your table (e.g., employees).
Add page items (P1_EMP_ID, P1_EMP_NAME, P1_DEPARTMENT, P1_SALARY).
Add a Submit Button.
Add a PL/SQL Process
Navigate to Processing > Create a New Process.
Select PL/SQL Process and enter the following code:
BEGIN
INSERT INTO employees (emp_id, emp_name, department, salary)
VALUES (:P1_EMP_ID, :P1_EMP_NAME, :P1_DEPARTMENT, :P1_SALARY);
COMMIT;
END;
This process executes when the user clicks Submit, inserting a new row.
Bind variables (:P1_EMP_ID, etc.) retrieve values from form fields.
3. Inserting a Row Using a Dynamic Action
You can insert a row dynamically using Dynamic Actions when a button is clicked.
Steps to Set Up Dynamic Action for Insert
Create a Button (e.g., Insert Data)
Go to Dynamic Actions and create a new True Action
Action: Execute PL/SQL Code
PL/SQL Code:
BEGIN
INSERT INTO employees (emp_id, emp_name, department, salary)
VALUES (:P1_EMP_ID, :P1_EMP_NAME, :P1_DEPARTMENT, :P1_SALARY);
COMMIT;
END;
Set Items to Submit
Select P1_EMP_ID, P1_EMP_NAME, P1_DEPARTMENT, P1_SALARY
These values will be passed into the query.
When the button is clicked, the new employee record is inserted without submitting the page.
4. Inserting Data Using a RESTful API Call
APEX allows inserting a row via REST services, useful when integrating with external applications.
Example REST API for Inserting Data
POST /ords/hr/employees/
{
"emp_id": 102,
"emp_name": "Jane Smith",
"department": "HR",
"salary": 5500
}
This method enables data insertion via API calls from external systems.
5. Inserting Data Using a Custom PL/SQL Procedure
If inserting rows is a frequent operation, use a PL/SQL procedure for reusability.
Creating a Stored Procedure for Insertion
CREATE OR REPLACE PROCEDURE add_employee (
p_emp_id IN NUMBER,
p_emp_name IN VARCHAR2,
p_department IN VARCHAR2,
p_salary IN NUMBER
) AS
BEGIN
INSERT INTO employees (emp_id, emp_name, department, salary)
VALUES (p_emp_id, p_emp_name, p_department, p_salary);
COMMIT;
END add_employee;
/
Calling the Procedure in APEX
BEGIN
add_employee(:P1_EMP_ID, :P1_EMP_NAME, :P1_DEPARTMENT, :P1_SALARY);
END;
This allows inserting data using a PL/SQL function call.
Best Practices for Adding Rows in APEX
Use Bind Variables to prevent SQL injection (:P1_EMP_ID).
Enable Error Handling to handle duplicate keys or missing values.
Use Commit Statements to ensure data persistence.
Validate Input Fields to avoid incorrect or missing data.
Adding a row using SQL in Oracle APEX can be done in multiple ways, depending on the use case. Whether using SQL Commands, PL/SQL processes, Dynamic Actions, RESTful APIs, or Stored Procedures, APEX provides flexibility for inserting data efficiently.
