Introduction
In Oracle APEX, procedures are a foundational component for structuring and reusing your application’s business logic. A procedure is a named PL/SQL block stored in the database, designed to perform specific tasks, such as inserting records, updating data, or performing calculations. Procedures help developers build modular, maintainable, and scalable applications. Whether you are developing a small internal tool or a large enterprise-grade system, knowing how to create and use procedures efficiently is a critical APEX development skill.
What Are Procedures in Oracle APEX
Procedures in Oracle APEX are standard PL/SQL stored procedures that reside in the Oracle database schema. They are composed of a header (with the procedure name and parameters) and a body (with the executable code). You can create them using SQL Workshop or other PL/SQL editors, and they can be called from various parts of your APEX application, such as page processes, validations, dynamic actions, or RESTful web services.
How to Create a Procedure in Oracle APEX – Step-by-Step Guide
-
Log into Your APEX Workspace
-
Navigate to SQL Workshop.
-
Click on Object Browser or use SQL Scripts > Create.
-
-
Write the Procedure Definition
A procedure typically includes:-
A name
-
Optional parameters
-
Executable statements
-
Exception handling
-
Example 1: Procedure without parameters
CREATE OR REPLACE PROCEDURE log_user_login IS
BEGIN
INSERT INTO login_audit (user_name, login_time)
VALUES (USER, SYSDATE);
END;
Example 2: Procedure with parameters
CREATE OR REPLACE PROCEDURE add_employee (
p_name IN VARCHAR2,
p_dept_id IN NUMBER,
p_salary IN NUMBER
) IS
BEGIN
INSERT INTO employees (employee_name, department_id, salary)
VALUES (p_name, p_dept_id, p_salary);
END;
-
Compile the Procedure
-
Use Run in SQL Workshop to execute the
CREATE OR REPLACEscript. -
Ensure there are no syntax errors. Errors will be shown in the output section.
-
-
How to Use a Procedure in Oracle APEX
Once the procedure is compiled successfully, you can call it from different parts of your APEX application:A. Page Process (After Submit)
-
Go to the APEX page > Processing > Create a new PL/SQL process.
-
Example:
BEGIN add_employee(:P1_NAME, :P1_DEPT_ID, :P1_SALARY); END;
B. Dynamic Action > Execute PL/SQL Code
-
In the PL/SQL code section:
log_user_login; -
Make sure to specify the items to submit and return (if applicable).
C. Validations and Computations
-
You can embed procedure calls within PL/SQL code blocks for server-side validation logic or item defaulting.
D. Scheduler or Backend Jobs
-
Procedures can be scheduled to run in the background using
DBMS_SCHEDULER.
E. RESTful Web Services
-
Use stored procedures as logic layers behind REST APIs exposed via Oracle REST Data Services (ORDS).
-
Extensive Example – Procedure and Application Integration
Assume you are building an employee management application. You want a form to insert new employee records into the EMPLOYEES table.
1. Create the Procedure
CREATE OR REPLACE PROCEDURE insert_employee_record (
p_emp_name IN VARCHAR2,
p_emp_email IN VARCHAR2,
p_emp_dept IN NUMBER
) IS
BEGIN
INSERT INTO employees (name, email, department_id, created_on)
VALUES (p_emp_name, p_emp_email, p_emp_dept, SYSDATE);
END;
2. In APEX Form Page – After Submit Process
BEGIN
insert_employee_record(:P2_NAME, :P2_EMAIL, :P2_DEPT_ID);
END;
3. Optional: Add Exception Handling in the Procedure
CREATE OR REPLACE PROCEDURE insert_employee_record (
p_emp_name IN VARCHAR2,
p_emp_email IN VARCHAR2,
p_emp_dept IN NUMBER
) IS
BEGIN
INSERT INTO employees (name, email, department_id, created_on)
VALUES (p_emp_name, p_emp_email, p_emp_dept, SYSDATE);
EXCEPTION
WHEN OTHERS THEN
-- Log the error or raise custom error
raise_application_error(-20001, 'Error inserting employee: ' || SQLERRM);
END;
A stored procedure is a named PL/SQL block that can be executed multiple times. It contains SQL and PL/SQL statements and can accept parameters to perform a specific task. Stored procedures are useful for modular programming, data validation, and complex business logic in Oracle APEX applications.
What is a Stored Procedure?
A stored procedure is a precompiled block of PL/SQL code stored in the database. It can:
Accept input parameters and return output parameters
Execute SQL statements like INSERT, UPDATE, DELETE, and SELECT
Perform complex business logic
Improve performance by reducing redundant code execution
Creating a Stored Procedure in Oracle
Basic Syntax for Creating a Stored Procedure
CREATE OR REPLACE PROCEDURE procedure_name (
parameter1 IN data_type,
parameter2 OUT data_type
) AS
BEGIN
-- SQL and PL/SQL statements
END procedure_name;
/
IN Parameter: Accepts input values.
OUT Parameter: Returns output values.
IN OUT Parameter: Can accept input and return updated values.
Example 1: Creating a Simple Stored Procedure
This procedure inserts a new employee into the employees table.
CREATE OR REPLACE PROCEDURE add_employee (
p_emp_id IN NUMBER,
p_emp_name IN VARCHAR2,
p_salary IN NUMBER
) AS
BEGIN
INSERT INTO employees (emp_id, emp_name, salary)
VALUES (p_emp_id, p_emp_name, p_salary);
COMMIT;
END add_employee;
/
How This Works
Takes three input parameters: p_emp_id, p_emp_name, and p_salary.
Inserts a new row into the employees table.
Uses COMMIT to save the changes.
How Do I Execute the Stored Procedure in APEX
1. Calling a Stored Procedure from SQL Commands
After creating the procedure, you can call it from SQL Commands in Oracle APEX.
BEGIN
add_employee(101, 'Alice Johnson', 6000);
END;
/
This inserts an employee with ID 101, name Alice Johnson, and salary 6000.
2. Calling a Stored Procedure in a PL/SQL Process in APEX
To use the stored procedure in an APEX form:
Create a form with three text fields:
P1_EMP_ID
P1_EMP_NAME
P1_SALARY
Add a Submit Button
Create a PL/SQL Process that Calls the Procedure
BEGIN
add_employee(:P1_EMP_ID, :P1_EMP_NAME, :P1_SALARY);
END;
When the user enters values in the form and clicks Submit, the procedure inserts the data into the table.
Example 2: Stored Procedure with an OUT Parameter
This procedure retrieves the salary of an employee based on the employee ID.
CREATE OR REPLACE PROCEDURE get_employee_salary (
p_emp_id IN NUMBER,
p_salary OUT NUMBER
) AS
BEGIN
SELECT salary INTO p_salary FROM employees WHERE emp_id = p_emp_id;
END get_employee_salary;
/
Calling the Procedure in PL/SQL
DECLARE
v_salary NUMBER;
BEGIN
get_employee_salary(101, v_salary);
DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || v_salary);
END;
/
This fetches the salary of employee 101 and displays it using DBMS_OUTPUT.PUT_LINE.
How Do I Use a Stored Procedure in an APEX Report
1. Using a Stored Procedure in a SQL Query
If a stored procedure returns a single value, it cannot be directly used in an Interactive Report or Classic Report. However, if it returns a table, it can be queried as follows:
SELECT emp_id, emp_name, salary FROM employees
WHERE salary > (SELECT salary FROM employees WHERE emp_id = 101);
If the stored procedure returns a table, use:
SELECT * FROM TABLE(my_stored_procedure(:P1_EMP_ID));
2. Using a Stored Procedure in a RESTful Web Service
Oracle APEX allows using stored procedures in RESTful Web Services to return JSON responses.
Create a RESTful Web Service in APEX
Set the SQL Query as:
BEGIN
get_employee_salary(:P1_EMP_ID, :P1_SALARY);
END;
Return JSON Output:
{
"EMP_ID": 101,
"SALARY": 6000
}
Example 3: Updating Data with a Stored Procedure
This procedure updates an employee's salary.
CREATE OR REPLACE PROCEDURE update_employee_salary (
p_emp_id IN NUMBER,
p_new_salary IN NUMBER
) AS
BEGIN
UPDATE employees SET salary = p_new_salary WHERE emp_id = p_emp_id;
COMMIT;
END update_employee_salary;
/
Calling the Procedure from APEX
Create an APEX Form with:
P1_EMP_ID
P1_NEW_SALARY
Create a PL/SQL Process:
BEGIN
update_employee_salary(:P1_EMP_ID, :P1_NEW_SALARY);
END;
Submit the form to update the salary.
Best Practices for Using Stored Procedures in APEX
Use Exception Handling – Prevent runtime errors.
Optimize Queries – Ensure queries inside procedures use indexes for better performance.
Avoid Hardcoded Values – Use dynamic parameters instead.
Example with Exception Handling
CREATE OR REPLACE PROCEDURE safe_add_employee (
p_emp_id IN NUMBER,
p_emp_name IN VARCHAR2,
p_salary IN NUMBER
) AS
BEGIN
INSERT INTO employees (emp_id, emp_name, salary)
VALUES (p_emp_id, p_emp_name, p_salary);
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20001, 'Employee ID already exists.');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002, 'An unexpected error occurred.');
END safe_add_employee;
/
This ensures the procedure does not fail if an employee ID already exists.
Stored procedures in Oracle APEX allow for modular, reusable, and efficient PL/SQL code execution. They improve performance and maintainability by separating logic from the application interface.
By integrating stored procedures in forms, reports, dynamic actions, and RESTful APIs, you can build robust and scalable APEX applications.
Best Practices
-
Use
CREATE OR REPLACEwhen developing to avoid duplication. -
Use input parameters for flexibility.
-
Avoid hardcoding values; use bind variables or page items.
-
Always use exception handling to manage unexpected runtime errors.
-
Keep procedures modular. One procedure should do one thing.
-
Avoid including business logic directly in pages – encapsulate it in procedures.
-
Write test scripts for procedures to ensure expected behavior.
Oracle APEX Documentation Link
For more on PL/SQL and using procedures in Oracle APEX, visit:
Oracle APEX SQL and PL/SQL Guide
Conclusion
Procedures are essential for organizing, maintaining, and reusing business logic across your Oracle APEX applications. By creating procedures, you ensure that logic is centrally managed, secure, and easier to maintain. Whether you are inserting data, managing sessions, or performing complex validations, procedures are a powerful tool in your APEX toolkit. Investing time in writing well-structured, documented procedures will help improve the performance and reliability of your applications.
