Pipelined function consumes memory

Hi,

I have a problem with a pipelined function. Maybe it's a general problem of understanding.

Please check the following script:

CREATE TYPE date_list IS TABLE OF DATE;

CREATE OR REPLACE FUNCTION pipe_date (dtStart DATE, dtEnd DATE)

   RETURN date_list DETERMINISTIC PIPELINED

IS

   iDays NUMBER;
BEGIN
   iDays := dtEnd - dtStart + 1;

   FOR i IN 0 .. iDays - 1
   LOOP
      PIPE ROW (dtStart + i);
   END LOOP;    RETURN;
END;
/

The function pipe_date returns all date values between start and end. You can check it with:

SELECT * FROM TABLE(PIPE_DATE(TO_DATE('01.01.2004', 'DD.MM.YYYY'), TO_DATE('31.12.2004', 'DD.MM.YYYY'))); It works as expected. But now I call this function several thousand times in a loop (it's a little bit more complicated). Every call of the pipe_date function increases the memory consumption of oracle.exe. On my laptop I reach the limit of memory in a few minutes. I don't understand this. Isn't the result kept only temporarily in memory? If i call a normal 'SELECT * FROM emp' several times in a loop, the memory consumption doesn't change.

How can I change the behaviour? I chose the pipelined function because of its much better performance over a 'select... from all_objects where...' to generate the date values.

Thanks,
Thomas   Received on Sat Nov 27 2004 - 09:02:01 CST