How Do I Create a stored procedure for displaying SELECT from a table in a report

 Introduction

Creating a stored procedure to perform a SELECT query from a table and display the results in an Oracle APEX report is a useful technique when you want to centralize complex query logic or return custom result sets. Instead of embedding SQL directly in the report region, you can write a PL/SQL stored procedure that returns a ref cursor. This approach improves code maintainability, separates business logic from the UI, and gives you greater control over how data is retrieved and presented.

In Oracle APEX, you can use a stored procedure with a ref cursor to return data from a table and display it in a report region. This approach is useful when you want to encapsulate complex query logic in the database and reuse it across multiple pages or applications. The procedure returns a result set using a SYS_REFCURSOR, which APEX can then use as the source for a report.

Step 1: Open SQL Workshop
Log in to Oracle APEX and go to SQL Workshop. Click on SQL Commands to create the stored procedure.

Step 2: Create the Stored Procedure
Use SQL to define a procedure that returns a ref cursor. Here is an example that selects data from the EMPLOYEES table:

CREATE OR REPLACE PROCEDURE get_employees (
  p_result OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN p_result FOR
    SELECT employee_id, first_name, last_name, email, hire_date
    FROM employees
    ORDER BY hire_date DESC;
END;

This procedure defines an OUT parameter p_result that holds the result set. Inside the procedure, the cursor is opened with a SELECT statement from the employees table.

Step 3: Create a Report Region in APEX
Go to your APEX application, open the page where you want the report, and enter Page Designer.

1. Create a new region.

2. Set the region type to Report → Classic Report or Interactive Report.

3. Set the Source Type to PL/SQL Function Returning SQL Query.

Step 4: Use the Stored Procedure in the Report Source
Enter the following code in the Source area:

DECLARE
  l_cursor SYS_REFCURSOR;
BEGIN
  get_employees(l_cursor);
  RETURN l_cursor;
END;

This block calls the stored procedure and returns the ref cursor as the report data source.

Step 5: Run and Test the Page
Click Run. Your report should display the data returned by the stored procedure. If the procedure is valid and the table has data, the results will be shown in the report region.

Optional Step: Add Parameters
You can modify the procedure to accept parameters for filtering. For example:

CREATE OR REPLACE PROCEDURE get_employees_by_dept (
  p_dept_id IN NUMBER,
  p_result OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN p_result FOR
    SELECT employee_id, first_name, last_name, email
    FROM employees
    WHERE department_id = p_dept_id;
END;

Then in APEX, you can pass a page item like this:

DECLARE
  l_cursor SYS_REFCURSOR;
BEGIN
  get_employees_by_dept(:P1_DEPT_ID, l_cursor);
  RETURN l_cursor;
END;

Best Practices
Use clear and meaningful names for procedures and parameters.
Add exception handling if needed.
Keep the SELECT statement optimized for performance.
Use bind variables instead of hardcoding values.
Recompile procedures if you change table structures or logic.

Using a stored procedure with a ref cursor to display data in a report allows you to keep SQL logic in the database, improve reusability, and simplify maintenance across your Oracle APEX application.

Conclusion

Using a stored procedure to return data for a report allows you to organize and reuse query logic while keeping your APEX pages cleaner and more modular. It is especially helpful for complex data retrieval or when the same logic needs to be used across multiple reports. By leveraging ref cursors and PL/SQL procedures, you create a more flexible and scalable foundation for your Oracle APEX applications.