How Do I Execute the Stored Procedure in APEX

 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:

1. Create a form with three text fields: 

• P1_EMP_ID

• P1_EMP_NAME

• P1_SALARY

2. Add a Submit Button

3. 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.