Pipelined and Parallel Table Functions

Pipelined and Parallel Table Functions (Table Function): This is a sample function (ppf_wip_op_seq) that creates a list of operation sequence numbers grouped by wip_entity_id and wip_entity_name using the Oracle ERP WIP schema tables. The input to a table function can be a collection of rows (ref cursor) and can output a collection of rows as required by the application. These functions achieve high performance by three key features - streaming, pipelining and parallel executing. The data processed by the table function is piped out (PIPE ROW(v_result) as shown below) in a iterative manner thereby optimizing the usage of memory.

Note: This is a guideline for creating a parallel pipeline function and the functionality of the function shown below can be performed efficiently by data concatenation functions

Package to create a Pipelined and Parallel Table Function

CREATE OR REPLACE PACKAGE get_data AS
 TYPE i_wip_rec IS RECORD
  (we_id          NUMBER,
   we_name        VARCHAR2(240),
   op_seq_num     NUMBER,
   str_delimiter  VARCHAR2(1)
  );

 TYPE i_wip_cur IS REF CURSOR RETURN i_wip_rec; 

 TYPE o_wip_rec IS RECORD 
  (we_id            NUMBER,
   we_name          VARCHAR2(240),
   op_seq_list      VARCHAR2(4000)
  );

 TYPE o_wip_tab IS TABLE OF o_wip_rec;

 FUNCTION ppf_wip_op_seq(cur i_wip_cur)
  RETURN o_wip_tab
  PIPELINED ORDER cur BY(we_id, we_name, op_seq_num) 
  PARALLEL_ENABLE(PARTITION cur BY HASH(we_id, we_name, op_seq_num)); 

END get_data;
/

CREATE OR REPLACE PACKAGE BODY get_data  AS

 FUNCTION ppf_wip_op_seq(cur i_wip_cur)
  RETURN o_wip_tab
  PIPELINED ORDER cur BY(we_id, we_name, op_seq_num)
  PARALLEL_ENABLE(PARTITION cur BY HASH(we_id, we_name, op_seq_num))
 AS
  v_we_id          NUMBER;
  v_we_name        VARCHAR2(240);
  v_op_seq         NUMBER;
  v_we_list        VARCHAR2(4000);
  v_str_delimiter  VARCHAR2(1);
  v_rec            i_wip_rec;
  v_result         o_wip_rec;

 BEGIN
  LOOP
    FETCH cur INTO v_rec;
    EXIT WHEN cur%NOTFOUND;

      -- Set the values from the first record
      IF v_we_id IS NULL THEN
        v_we_id := v_rec.we_id;
        v_we_name := v_rec.we_name;
        v_op_seq := v_rec.op_seq_num;
        v_we_list := v_rec.op_seq_num;
        v_str_delimiter := v_rec.str_delimiter;
      END IF;

      IF v_we_id = v_rec.we_id THEN

         IF v_op_seq != v_rec.op_seq_num  THEN
           v_we_list := v_we_list||v_str_delimiter||v_rec.op_seq_num;
         END IF;

      ELSE

         v_result.we_id := v_we_id;
         v_result.we_name := v_we_name;
         v_result.op_seq_list := v_we_list;
         PIPE ROW (v_result);
         v_we_id := v_rec.we_id;
         v_we_name := v_rec.we_name;
         v_op_seq := v_rec.op_seq_num;
         v_we_list := v_rec.op_seq_num;

      END IF;

  END LOOP;

  -- Pipe out last value

  v_result.we_id := v_we_id;
  v_result.we_name := v_we_name;
  v_result.op_seq_list := v_we_list;
  PIPE ROW (v_result);
  RETURN;
  CLOSE cur;

 EXCEPTION
   WHEN OTHERS THEN  RAISE;
 END ppf_wip_op_seq;

END get_data;
/

Table Function Call

SELECT
  q_tab.we_id  wip_entity_id, q_tab.we_name  wip_entity_name, q_tab.op_seq_list
FROM TABLE(get_data.ppf_wip_op_seq(CURSOR(
            SELECT 
              we.Wip_Entity_Id, We.Wip_Entity_Name, wo.operation_seq_num, '|'
            FROM  wip_operations wo,
                  wip_discrete_jobs Wdj,
                  wip_entities We
            WHERE wdj.organization_id = wo.organization_id
            AND   wdj.organization_id = we.organization_id
            AND   wdj.wip_entity_id = wo.wip_entity_id
            AND   wdj.wip_entity_id = we.wip_entity_id))
           ) q_tab
WHERE ROWNUM < 10;

Back

Oracle registered trademark of Oracle Corporation.

55756