Oracle PL/SQL for DBAs

Pipelined Table Functions

A pipelined table function is one that returns a result set as a collection, but it does so iteratively. In other words, Oracle does not wait for the function to run to completion, storing all the rows it computes in the PL/SQL collection before returning it. Instead, as each row is ready to be assigned to the collection, it is “piped out” of the function. Let’s see a pipelined table function in action.

    /* File on web: date_parser_pipelined.sql */
    CREATE OR REPLACE FUNCTION date_parse ( p_curs SYS_REFCURSOR )
                      RETURN order_date_t
                      PIPELINED AS

      v_order_rec orders%ROWTYPE;

    BEGIN

      -- for every order in the cursor...
      LOOP
        FETCH p_curs INTO v_order_rec;
        EXIT WHEN p_curs%NOTFOUND;

        -- pipe out the components of the orders open data
        PIPE ROW(order_date_o(v_order_rec.order_number,
                               'O',
                               TO_CHAR(v_order_rec.create_date,'YYYY'),
                               TO_CHAR(v_order_rec.create_date,'Q'),
                               TO_CHAR(v_order_rec.create_date,'MM')));

        -- pipe out the components of the orders assign date
        PIPE ROW(order_date_o(v_order_rec.order_number,
                              'A',
                              TO_CHAR(v_order_rec.assign_date,'YYYY'),
                              TO_CHAR(v_order_rec.assign_date,'Q'),
                              TO_CHAR(v_order_rec.assign_date,'MM')));

        -- pipe out the components of the orders close date
        PIPE ROW(order_date_o(v_order_rec.order_number,
                              'C',
                              TO_CHAR(v_order_rec.close_date,'YYYY'),
                              TO_CHAR(v_order_rec.close_date,'Q'),
                              TO_CHAR(v_order_rec.close_date,'MM')));

      END LOOP;  -- every order in the cursor

      RETURN;

    END;

There are four main syntax changes between the non-pipelined version and ...