Pipelined Table Functions

Table functions are used in FROM clause of a query as if it were a regular table that return PL/SQL collections. Returning large size of collections can be cause performance issue because of collections are held in memory. Pipelined table functions come into play at this point. It can prevent the need to build huge collections on memory. It works asynchronously to the termination of the function. Database no need to wait for the function in order to finish completely. Each row can be returned instantly by using PIPE ROW clause. So let’s start with to create a simple table function.

CREATE TYPE TYP_TEST AS OBJECT 
(
ID NUMBER, 
TEXT VARCHAR(50)
);

CREATE TYPE ARR_TEST IS TABLE OF TYP_TEST;


CREATE OR REPLACE PACKAGE PKG_TEST
IS
    
    FUNCTION MY_F(P_NUM NUMBER DEFAULT 1) RETURN ARR_TEST;
    
END;
CREATE OR REPLACE PACKAGE BODY PKG_TEST
IS  

    FUNCTION MY_F(P_NUM NUMBER DEFAULT 1) 
    RETURN ARR_TEST
    IS
        MY_ARR ARR_TEST := ARR_TEST();
    BEGIN

        FOR I IN 1..P_NUM
        LOOP    
            MY_ARR.EXTEND;
            MY_ARR(MY_ARR.LAST) := TYP_TEST(I, 'Text number '||I);
        END LOOP;

        RETURN MY_ARR;
    END;
    
END;
SELECT * FROM TABLE(PKG_TEST.MY_F(10));

You can query table functions with TABLE operator as shown above. In addition, as of 12.2 you can use without TABLE operator.

SELECT * FROM PKG_TEST.MY_F(10);

Pipelined Table Function:

As it can be noticed. I added PIPELINED keyword after return type.

DROP TYPE ARR_TEST;
DROP TYPE TYP_TEST;
DROP PACKAGE PKG_TEST;

CREATE OR REPLACE PACKAGE PKG_TEST
IS
    TYPE REC_TEST IS RECORD(
        ID NUMBER,
        TEXT VARCHAR2(50)
    );
    
    TYPE TYP_ARR IS TABLE OF REC_TEST;
    
    FUNCTION MY_F(P_NUM NUMBER DEFAULT 1) RETURN TYP_ARR PIPELINED;
    
END;
CREATE OR REPLACE PACKAGE BODY PKG_TEST
IS
  
    FUNCTION MY_F(P_NUM NUMBER DEFAULT 1) 
    RETURN TYP_ARR PIPELINED
    IS
        MY_ARR TYP_ARR := TYP_ARR();
    BEGIN
        FOR I IN 1..P_NUM
        LOOP
            MY_ARR.EXTEND;
            MY_ARR(MY_ARR.LAST).ID := I;
            MY_ARR(MY_ARR.LAST).TEXT := 'Text number '||I;
            PIPE ROW(MY_ARR(MY_ARR.LAST));
        END LOOP;
        RETURN;
    END;
      
END;

Normally, return type of the table functions should be created at the schema level. Because of context switching between SQL and PL/SQL. However, in the example above nested table was created in the package specification. You may wonder that how can it work without declaring at schema level. Oracle created the required types implicitly. So, let’s check the USER_OBJECTS.

SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TYPE';

In addition, using collections in pipelined table functions are not required. In other words, this approach gets the job done. However, you don’t have to populate collections in pipelined table functions. In order to prevent excessive usage of PGA memory, the following utilization is much more better. As you can see, the collection was created only for the declaration of the return type of function.

DROP PACKAGE PKG_TEST;
DROP TYPE ARR_TEST;
DROP TYPE TYP_TEST;
CREATE TYPE TYP_TEST AS OBJECT 
(
ID NUMBER, 
TEXT VARCHAR(50)
);
 
CREATE TYPE ARR_TEST IS TABLE OF TYP_TEST;
CREATE OR REPLACE PACKAGE PKG_TEST
IS
    FUNCTION MY_F(P_NUM NUMBER DEFAULT 1) RETURN ARR_TEST PIPELINED;  
END;
CREATE OR REPLACE PACKAGE BODY PKG_TEST
IS
   
    FUNCTION MY_F(P_NUM NUMBER DEFAULT 1) 
    RETURN ARR_TEST PIPELINED
    IS
    BEGIN
        FOR I IN 1..P_NUM
        LOOP
            PIPE ROW(TYP_TEST(I, 'Text number '||I));
        END LOOP;
        RETURN;
    END;
       
END;

I will cover parallel execution of pipelined table functions in my next article.

Leave a Reply

Your email address will not be published. Required fields are marked *