Oracle Solutions We believe in delivering tangible results for our customers in a cost-effective manner
When developing Oracle APEX applications, you’ll frequently encounter Processes, Procedures, and Packages. Although they all execute business logic, they are designed for different purposes.
Understanding when to use each one is essential for building clean, maintainable, and scalable applications.
In this article, we’ll explore what each object is, how they differ, and when you should choose one over another.
What is a Process?
A Process is an Oracle APEX component that executes business logic at a specific point during a page’s lifecycle.
Unlike procedures or packages, a process is not a database object. It exists within an APEX application and is responsible for actions such as inserting data, updating records, sending emails, calling PL/SQL code, or performing validations.
A process can execute:
SQL
PL/SQL
Calls to procedures
Calls to package procedures
Example
A page contains an “Add Employee” button.
When the user clicks Save, the process executes:
INSERT INTO EMPLOYEES ( EMP_NAME,SALARY)
VALUES
( :P10_EMP_NAME, :P10_SALARY);
The process runs only when the button is pressed.
What is a Procedure?
A Procedure is a standalone PL/SQL program stored inside the Oracle Database.
It performs one specific task and can accept parameters.
Procedures are reusable and can be called from:
Oracle APEX
SQL Developer
Other procedures
Packages
Database jobs
Example
CREATE OR REPLACE PROCEDURE UPDATE_EMPLOYEE
(
P_EMP_ID NUMBER,
P_SALARY NUMBER
)
AS
BEGIN
UPDATE EMPLOYEES
SET SALARY = P_SALARY
WHERE EMP_ID = P_EMP_ID;
END;
Calling it from APEX:
BEGIN
UPDATE_EMPLOYEE
(
:P10_EMP_ID,
:P10_SALARY
);
END;
What is a Package?
A Package is a collection of related procedures, functions, variables, constants, cursors, and exceptions grouped together under a single name.
Instead of creating many standalone procedures, packages organize related business logic into one module.
A package contains:
Package Specification
Package Body
Example
EMPLOYEE_PKG
ADD_EMPLOYEE
UPDATE_EMPLOYEE
DELETE_EMPLOYEE
GET_EMPLOYEE
CALCULATE_SALARY
Now all employee-related operations are managed in one place.
Understanding the Relationship
Think of them like this:
Process → Executes business logic from the APEX page.
Procedure → Performs one reusable database task.
Package → Organizes multiple related procedures and functions into a single module.
A process can call a procedure, and a procedure can also belong to a package.
For example:
Save Button
│
▼
APEX Process
│
▼
EMPLOYEE_PKG.ADD_EMPLOYEE
│
▼
Insert Record into Database
This layered approach keeps APEX pages clean while centralizing business logic in the database.
When Should You Use a Process?
Use an APEX Process when:
A button is clicked.
A page is submitted.
Data needs to be validated.
An email needs to be sent.
A procedure or package needs to be called.
Temporary page-specific logic is required.
Avoid placing large amounts of business logic directly inside processes.
When Should You Use a Procedure?
Use a standalone procedure when:
The logic performs one specific task.
The code needs to be reused.
The application is relatively small.
There is no need to group related functionality.
Examples:
Update employee salary
Generate invoice
Close voucher
Calculate tax
When Should You Use a Package?
Packages are recommended when:
Building enterprise applications.
Multiple procedures belong to the same module.
Business logic is shared across many pages.
You need private helper functions.
Long-term maintenance is important.
Best Practice in Oracle APEX
One common mistake is writing all business logic directly inside APEX Processes.
A better architecture is:
APEX Page
│
▼
APEX Process
│
▼
Package Procedure
│
▼
Database Tables
This approach offers several benefits:
Cleaner APEX pages
Reusable business logic
Easier debugging
Better maintainability
Improved scalability
Best Practices
Keep APEX Processes lightweight.
Move business logic into procedures or packages.
Group related procedures into packages.
Avoid duplicating code across multiple APEX pages.
Use meaningful names such as EMPLOYEE_PKG, PAYROLL_PKG, or INVENTORY_PKG.
Reserve standalone procedures for simple, isolated operations.
Although Processes, Procedures, and Packages all execute logic, they serve different roles in Oracle development. An APEX Process is responsible for handling page events and user interactions, a Procedure performs a single reusable database operation, and a Package groups related procedures and functions into a structured module.
For small applications, standalone procedures may be sufficient. However, as applications grow, organizing business logic into packages while keeping APEX processes focused on orchestration leads to cleaner code, better performance, and easier maintenance. Adopting this layered architecture is a best practice for building professional Oracle APEX applications.
