Oracle – Return SELECT Statement Results Like SQL Server SP’s – Using Pipelined Functions

Note: You can find a simpler version of this in my answer on StackOverflow.

Returning SELECT statement results from procedures/functions?

Everyone is familiar with Oracle functions that return basic data types (simple values with no internal components). Although rarely used, Oracle functions are however capable of returning more complex data types like cursors, arrays and object tables.

How it is done in SQL Server:

SQL Server has had the capability to return the results of one or more select statements like this (since inception?)

--SQL Server procedure returning results from a SELECT
CREATE PROCEDURE usp_Select_Clients
AS
BEGIN
   SELECT * FROM CLIENT_MASTER;
END;

--Run the procedure to get the results
EXEC usp_Select_Clients

The results itself

CLIENT_CODE  CLIENT_ID   CLIENT_GROUP                                         AFFILIATE_ID
------------ ---------- ----------------------------------------------------- -------------
1            HJM        Semi-Liberty Group US                                 1
2            LEK        Full Liberty Group                                    2
3            LYN        Boston Logger Co Group                                3
4            KAT        ABC Incorporated                                      4
5            ASK        ASKncipal Life                                        5
6            EIK        AllPeople                                             6
7            ADE        America Benefit                                       7
8            DES        DES Life Insurance Company                            8
9            LIU        Dominose Life - LIUcia Group (INACTIVE)               9
10           PIE        PIEON USA (SCOR)                                      10
11           DOE        DOE Group                                             11

How it is done in Oracle traditionally:

In Oracle if we were to do something similar, we would have simply returned a cursor that the caller would then use.

--Create a function that return the results of a SELECT
CREATE OR REPLACE FUNCTION fnc_Get_Clients_Cursor
RETURN SYS_REFCURSOR
AS
my_cursor SYS_REFCURSOR;
BEGIN
   OPEN my_cursor FOR SELECT * FROM CLIENT_MASTER;
   RETURN my_cursor;
END fnc_Get_Clients;

Consume the function above that returns a cursor

DECLARE
   v_Cur SYS_REFCURSOR;
   v_Rec CLIENT_MASTER%ROWTYPE;
BEGIN
   v_cur := fnc_Get_Clients_Cursor();

   LOOP
      FETCH v_Cur INTO v_Rec;
      EXIT WHEN v_Cur%NOTFOUND; -- Exit when there is no more data
      DBMS_OUTPUT.PUT_LINE('Row: '|| v_Rec.CLIENT_Id || ' ' || v_Rec.CLIENT_Group);
   END LOOP;

   CLOSE v_Cur;
END;
/

The results:

Row: HJM Semi-Liberty Group US
Row: ASK ASKncipal Life
Row: ADE America Benefit
Row: DES DES Life Insurance Company
Row: LIU Dominose Life - LIUcia Group (INACTIVE)
Row: DOE DOE Group
Row: ALZ Allianz Life Insurance Company of North America
….

How could it be done in Oracle better?

This is great but it is not as easy as doing something like this, if it were possible:

SELECT * FROM fnc_Get_Clients_Cursor();

Pipelined functions:

Enter Oracle pipelined functions – They are designed to do exactly the above. Let us convert the above function that returns a cursor into a pipelined function:

Step 1: Create an object the represents one row of the results:

CREATE OR REPLACE TYPE CLIENT_type
AS OBJECT
(
   CLIENT_id VARCHAR(6),
   CLIENT_group VARCHAR(60)
);

Step 2: Create a collection (table type) of the object type created in step 1

CREATE OR REPLACE TYPE CLIENT_table_type
AS TABLE OF CLIENT_type;

Step 3: Create a function that returns the table type from step 3

CREATE OR REPLACE FUNCTION fnc_Get_Clients_Pipelined
RETURN CLIENT_table_type
PIPELINED
AS
BEGIN

   FOR v_Rec IN (SELECT * FROM CLIENT_MASTER) LOOP

      PIPE ROW (CLIENT_type(v_Rec.CLIENT_Id, v_Rec.CLIENT_Group));

   END LOOP;

RETURN;
END;

Step 4: Consume the function as if it was a “table” or “view”

SELECT CLIENT_id, CLIENT_group
FROM TABLE(fnc_Get_Clients_Pipelined());

CLIENT_ID CLIENT_GROUP
HJM Semi-Liberty Group US
ASK ASKncipal Life
ADE America Benefit
DES DES Life Insurance Company

Real-world usage: Convert procedures returning REF cursors into Pipelined functions

In the real world, typically, legacy functionality will exist in the form of functions returning cursors after performing some complex processing. It would be easy to wrap those functions using pipelined functions to use them with the more natural SELECT syntax.

Here we wrap the function that returns a cursor into a function that is piplelined.

CREATE OR REPLACE FUNCTION fnc_Get_Clients_PLWrapCursor
(
   p_Cur IN SYS_REFCURSOR
)
RETURN CLIENT_table_type
PIPELINED
AS
   v_Cur SYS_REFCURSOR;
   v_Rec CLIENT_MASTER%ROWTYPE;
BEGIN
   v_cur := fnc_Get_Clients_Cursor();

   LOOP
      FETCH v_Cur INTO v_Rec;
      EXIT WHEN v_Cur%NOTFOUND; -- Exit the loop when we've run out of data
      DBMS_OUTPUT.PUT_LINE('Row: '|| v_Rec.CLIENT_Id || ' ' || v_Rec.CLIENT_Group);

      PIPE ROW (CLIENT_type(v_Rec.CLIENT_Id, v_Rec.CLIENT_Group));
   END LOOP;
   CLOSE v_Cur;

   RETURN;
END fnc_Get_Clients_PLWrapCursor;

Client-side consumption:

Now we are cursor-syntax free….using a function that returns a cursor!

SELECT *
FROM TABLE(fnc_Get_Clients_PLWrapCursor(fnc_Get_Clients_Cursor));

This syntax is also easier to consume in .NET and Java.

Alternatively, we could have chosen to implement the function as one that returns the table type but that would make the .NET and Java consumption all the more difficult although it is not an issue to consume that within Oracle.