📊 From REF CURSOR to Report: Pipelined Functions in Oracle APEX Made Simple

-

🚩Introduction 

  • In Oracle APEX, Interactive and Classic Reports work with SQL queries, but procedures that return REF CURSORS cannot be used directly. This creates a gap when your data logic is written in PL/SQL but your report expects a query.

  • Pipelined table functions solve this by converting REF CURSOR output into a format that behaves like a table. This lets you reuse existing procedures while still feeding data into APEX reports using simple SQL, keeping your application clean and easy to maintain.

📑 Why This Approach Is Needed

In Oracle APEX, reports need SQL queries, but many times data may comes from procedures that return REF CURSORS. These cannot be used directly in reports, which makes it harder to show the data without changing existing code.

By using a pipelined table function, you can convert that data into a simple SQL query format. This helps you reuse existing logic, avoid repeating code, and automatically show updated data in reports. It keeps the application simple, flexible, and easier to maintain.

This approach is useful when:

  • You need to display data from a REF CURSOR in reports without rewriting existing procedures

  • You want to reuse PL/SQL logic instead of duplicating queries

  • Report data should change dynamically based on database values

  • You want to avoid hardcoding SQL logic in multiple places

  • The application should remain simple, flexible, and easy to maintain

By using this method, applications become more efficient, easier to manage, and allow seamless integration between PL/SQL logic and SQL-based reports in Oracle APEX with minimal code duplication.

👉 Use Case: Using Pipelined Function for Reports in Oracle APEX

In Oracle APEX, reports usually need simple SQL queries. But sometimes, data comes from procedures using REF CURSORS, which cannot be used directly in reports. Changing all that logic into SQL can be difficult and time-consuming.

In this blog, we will see how to use a pipelined function to convert that data into a format that works like a normal table. This helps you use existing code, avoid repeating logic, and easily show data in reports. It keeps your application simple, flexible, and easy to maintain.

Step 1: 

First, we need to create a procedure that returns data using a REF CURSOR in Oracle Database. This cursor will be used later in APEX through a pipelined function.
------ Ref Cursor ------
CREATE OR REPLACE PROCEDURE ref_cursor_proc (p_cursor OUT SYS_REFCURSOR) AS
BEGIN
    OPEN p_cursor FOR SELECT deptno, empno, ename, job, sal FROM emp ORDER BY 1, 2; 
END;

Step 2: 

Next, we need to create SQL object types. These types define the structure of the data that will be returned from the pipelined function. This step is required because a pipelined function must return data in a table-like format.
------ Object Type & Table Type for Pipelined Function ------

CREATE OR REPLACE TYPE emp_obj AS OBJECT (
    empno   NUMBER,
    deptno  NUMBER,
    ename   VARCHAR2(100),
    job     VARCHAR2(50),
    sal     NUMBER
);

CREATE OR REPLACE TYPE emp_tab AS TABLE OF emp_obj;

Step 3: 

Next, we will create a pipelined table function. This function takes the data from the REF CURSOR, converts each row into an object, and returns it in a table format that can be used directly in Oracle APEX Interactive or Classic Reports.

------ Pipelined Function ------

CREATE OR REPLACE FUNCTION get_emp_pipe
RETURN emp_tab PIPELINED
AS 
    l_cursor SYS_REFCURSOR;
    l_emp_id NUMBER;
    deptno NUMBER;
    ename VARCHAR2(75);
    job VARCHAR2(75);
    sal NUMBER;
BEGIN
    ref_cursor_proc (l_cursor); -- Calling the Procedure which returns ref cursor
    LOOP
        FETCH l_cursor INTO l_emp_id, deptno, ename, job, sal;
        EXIT WHEN l_cursor%NOTFOUND;

        PIPE ROW (emp_obj(l_emp_id, deptno, ename, job, sal));
    END LOOP;
    CLOSE l_cursor;
    RETURN;
END;

Step 4: 

Now, in the Page designer, create one region of type Interactive Report / Classic Report and under the source attribute select the type as SQL Query. Copy and paste the below sql query for calling the pipeline function for fetching the report data.

// Calling Pipeline Function

SELECT * FROM TABLE(get_emp_pipe);

Step 5: 

Now, Save and run the the Page, you will see the report region with the employee data.

🔥 Conclusion

Thus, in Oracle APEX, using a pipelined function helps you display data from REF CURSORs in Interactive and Classic Reports without rewriting your existing procedures. It converts the data into a simple SQL format that APEX can easily use. Overall, this approach makes your application cleaner, avoids duplicate code, and keeps things easy to maintain.


Comments

Post a Comment

Popular posts from this blog

🔍 Extending Smart Search Filter for Multiple Regions

-
Image
🚩Introduction  In Oracle APEX, the Smart Search filter is a convenient feature that allows users to quickly search data in a Classic Report or Cards region without writing custom code. However, this functionality is limited to a single region , which can be restrictive when a page contains multiple regions that should respond to the same search input. In this tutorial, you will learn how to extend the Smart Search filter to work with multiple regions on the same page . We will implement a solution that allows one Smart Search input to drive filtering across multiple Classic Report ,Cards regions etc. By the end of this tutorial, you’ll have a clear, reusable approach for coordinating search behavior across regions, improving both usability and consistency in your Oracle APEX applications without breaking the declarative nature of APEX. 📑 Why Extending the Smart Search Filter Is Needed The built-in Smart Search filter in Oracle APEX works well when a page contains a single sear...
Read more

💡 Designing Dynamic QuickPicks in Oracle APEX

-
Image
🚩Introduction  Oracle APEX provides Quick Picks as a convenient way to allow users to populate page items with predefined values using a single click. While static Quick Picks are useful, real-world applications often require these values to be generated dynamically based on business rules, user roles, or underlying data. This blog explores how to implement Dynamic Quick Picks in Oracle APEX page items , enabling developers to build more intelligent, flexible, and user-friendly forms by driving Quick Pick values directly from database queries and runtime logic.  📑 Why Dynamic Quick Picks Are Needed   Static Quick Picks in Oracle APEX are useful for simple filtering, but Dynamic Quick Picks become essential when: Filter values must be generated at runtime based on current data, user role, or context Applications require data-driven filters instead of hardcoded values The same Quick Picks must adapt across multiple pages or regions Filter options depend ...
Read more

📌Track Active Tab switch in Region Display Selector without any plugins

-
Image
🚩Introduction  In Oracle APEX applications, Region Display Selector components helps to organize multiple regions efficiently. However, APEX does not provide a built-in declarative option to detect which tab is active when users switch between them, making it challenging to trigger conditional logic based on tab selection. This can be solved by creating the custom dyanamic action to capture the tab change. This approach enables dynamic actions like refreshing regions or executing custom logic whenever a tab becomes active, without requiring any plugins. 📑Why It is Essential to Track Tab Selection in Region Display Selector In APEX applications, pages often organize multiple regions under a Region Display Selector to separate content like Details, History, or Reports. While this improves layout, users actions on different tabs often require specific logic to run, such as refreshing a region, loading dynamic content, or updating page items. Without capturing which tab is active, de...
Read more