Pipelined Table Functions (Table Function): This is a simple pipelined table function (pf_create_data) that creates a list of numbers. For testing, the output row is passed to a single value Factorial function.
Note: This is a guideline for creating a pipeline function and the functionality of the function shown below can be performed efficiently by querying DUAL table using the CONNECT BY LEVEL <= i_value syntax and passing the values to the factorial function.

Pipelined Table Function 
CREATE OR REPLACE TYPE typ_num_tab IS TABLE OF NUMBER;
/

CREATE OR REPLACE
 FUNCTION pf_create_data(i_num IN PLS_INTEGER)
  RETURN typ_num_tab DETERMINISTIC PIPELINED
AS
BEGIN
  FOR i IN 1..i_num LOOP
     PIPE ROW(FACTORIAL(i));
  END LOOP;
  RETURN;
EXCEPTION
 WHEN OTHERS THEN RAISE;
END pf_create_data;
/

-- Table Function Call

SELECT ROWNUM ref_number, q_tab.* 
FROM TABLE(pf_create_data(&i_val)) q_tab
;
  
Output for i_val of 9

Oracle registered trademark of Oracle Corporation.
Last Revised On: July 31, 2012

  73824