How Do I Use a Stored Procedure in an APEX Report

 Introduction

Using a stored procedure in an Oracle APEX report allows you to centralize complex data retrieval logic within the database, improving maintainability and performance. Instead of embedding lengthy SQL queries directly in your report region, you can write a stored procedure that returns a result set via a ref cursor. This approach provides flexibility to reuse the procedure across multiple reports and applications while keeping your APEX pages cleaner and more efficient.

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.

1. Create a RESTful Web Service in APEX

2. Set the SQL Query as:

BEGIN 

    get_employee_salary(:P1_EMP_ID, :P1_SALARY);

END;

Return JSON Output:

{

    "EMP_ID": 101,

    "SALARY": 6000

}

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

1. Create an APEX Form with: 

• P1_EMP_ID

• P1_NEW_SALARY

2. Create a PL/SQL Process:

BEGIN 

    update_employee_salary(:P1_EMP_ID, :P1_NEW_SALARY);

END;

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

Conclusion
Incorporating stored procedures into your APEX reports enhances modularity and simplifies application maintenance by separating data logic from presentation. By returning result sets through ref cursors, stored procedures offer a powerful way to manage complex queries and filtering within the database. Leveraging this method helps build scalable, reusable, and high-performing reports in your Oracle APEX applications.