Introduction
Executing a stored procedure in Oracle APEX allows you to leverage powerful database logic directly from your web application. Stored procedures can perform tasks like inserting, updating, or deleting data, as well as complex business operations. By calling these procedures within APEX page processes, buttons, or dynamic actions, you can keep your application code clean and maintainable while centralizing key logic in the database.
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.
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.
Conclusion
Calling stored procedures in Oracle APEX simplifies application development by separating business logic from the user interface. It enables reuse of database operations and improves performance by reducing redundant code. With simple PL/SQL blocks or processes, you can execute stored procedures efficiently, making your applications more scalable, secure, and easier to maintain.
