PIPELINED Function vs Normal Function Use Case in Oracle

Suppose we wanted to return values from Function in a nested table type format then pipelined table functions will give us better performance when compared with normal functions.

PGA memory consumption will be less in case of pipelined functions when compared it with normal functions


Example:
Create Object Type

create or replace type test_obj as object (id number, dsc varchar2(100));

Create Table Type

create or replace type test_obj_tab as table of test_obj;

Create Normal Function

CREATE OR REPLACE FUNCTION test_normal (p_val IN NUMBER)
   RETURN test_obj_tab
AS
   v_test_obj_tab   test_obj_tab := test_obj_tab ();
BEGIN
   FOR i IN 1 .. p_val
   LOOP
      v_test_obj_tab.EXTEND;
      v_test_obj_tab (v_test_obj_tab.LAST) := test_obj (i, 'Number-' || i);
   END LOOP;

   RETURN v_test_obj_tab;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (' error-  ' || SQLERRM);

END;

Create PIPELINED Function

CREATE OR REPLACE FUNCTION test_pipe (p_val IN NUMBER)

   RETURN test_obj_tab pipelined 

AS

   v_test_obj_tab   test_obj_tab := test_obj_tab ();

BEGIN

   FOR i IN 1 .. p_val

   LOOP

      pipe row (test_obj (i, 'Number-' || i));

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (' error-  ' || SQLERRM);

END;

Testing

select * from
table(test_normal (100));

select * from
table (test_pipe(100));