Bulk Collect and Pipelined Table Function

From: Peter Kallweit <peter.kallweit_at_vector-consulting.de>
Date: Fri, 25 Feb 2005 15:35:33 +0100
Message-ID: <388rdnF5mor4rU1@individual.net>

Hi all,

I have a table function which selects from a view with a where clause defined at runtime. The fetch works with bulk collect. Today this function is not pipelined. Therefore, the select where this table function is called, is sometimes quite slow. That's the function:

FUNCTION F_GET_FILTER_RESULT

          (sFILTER IN VARCHAR2
          )RETURN NUMBERTABLE_TYPE IS

TYPE RULECURSORTYPE IS REF CURSOR;
RULE_CURSOR RULECURSORTYPE;
RESOLVED_RULE NUMBERTABLE_TYPE;
BEGIN
   OPEN RULE_CURSOR FOR 'SELECT V_NR FROM SHOW_VERSION WHERE ' ||sFILTER;    FETCH RULE_CURSOR BULK COLLECT INTO RESOLVED_RULE;    CLOSE RULE_CURSOR;
EXCEPTION

     WHEN OTHERS THEN
       RESOLVED_RULE := NUMBERTABLE_TYPE();

END;
RETURN RESOLVED_RULE;
END F_GET_FILTER_RESULT; Now I'd like to change this function into a pipelined table function. AFAIK, to do the pipelining I have to work with 'pipe row' for each single row.
But I don't have / want the single row, because I fetch with bulk collect. How can I resolve this contradiction?

Btw., I'm working with 9.2.0.5 on W2K.

Regards
Peter Received on Fri Feb 25 2005 - 08:35:33 CST