Font: Arial, 14px, Plain Text
In Oracle APEX, PL/SQL procedures are named blocks of code that perform specific tasks and can be called multiple times from different parts of your application. Using procedures promotes reusability by allowing you to write logic once and reuse it wherever needed. This reduces code duplication, simplifies maintenance, and improves application organization.
Step 1: Creating a Procedure
You create a procedure using the CREATE OR REPLACE PROCEDURE statement in SQL Workshop or your database environment.
CREATE OR REPLACE PROCEDURE update_salary (
p_employee_id IN NUMBER,
p_new_salary IN NUMBER
) IS
BEGIN
UPDATE employees
SET salary = p_new_salary
WHERE employee_id = p_employee_id;
COMMIT;
END;
Step 2: Calling a Procedure
You can call the procedure from PL/SQL blocks, page processes, dynamic actions, or validations within APEX.
BEGIN
update_salary(:P1_EMPLOYEE_ID, :P1_NEW_SALARY);
END;
Step 3: Passing Parameters
Procedures can accept IN, OUT, or IN OUT parameters to receive inputs and return outputs.
-
IN parameters are for input values.
-
OUT parameters return values from the procedure.
-
IN OUT parameters can pass values in and out.
Example with an OUT parameter:
CREATE OR REPLACE PROCEDURE get_employee_name (
p_employee_id IN NUMBER,
p_employee_name OUT VARCHAR2
) IS
BEGIN
SELECT first_name || ' ' || last_name
INTO p_employee_name
FROM employees
WHERE employee_id = p_employee_id;
END;
Step 4: Using Procedures in APEX
-
Create procedures in your database using SQL Workshop or external tools.
-
Call them inside page processes, validations, or computations by writing anonymous PL/SQL blocks.
-
Pass page items as parameters using colon notation (e.g.,
:P1_ITEM). -
Use procedures to centralize business logic and simplify application development.
Step 5: Best Practices
-
Keep procedures focused on single tasks for better modularity.
-
Use meaningful names that describe what the procedure does.
-
Handle exceptions inside procedures to avoid unhandled errors.
-
Document your procedures with comments for easier maintenance.
-
Test procedures independently before integrating them into APEX pages.
Using PL/SQL procedures in Oracle APEX improves code reusability, reduces duplication, and helps maintain a clean and manageable codebase. They are fundamental building blocks for scalable and efficient application development.
