Why doesn't this cure my ORA-04030?

From: prl <prlawrence_at_lehigh.edu>
Date: Thu, 12 Feb 2004 13:45:39 -0500
Message-ID: <c0ghju$f5s$1@fidoii.CC.Lehigh.EDU>

I'm using

   FETCH ... BULK COLLECT INTO
and then subsequently

   FORALL ... INSERT INTO Very cool, very fast. However, I found that if my base population was too large, that I would get ORA-04030 somewhere in the process.

So, I decided to loop the entire process and just do a portion of the base population on each pass.

I can go through a number of passes in this manner, but I still end up getting ORA-04030. Why? Shouldn't the TABLEs I'm BULK COLLECTing into release their memory after each pass? Each pass has no more data involved than the first, and that one (and many after) go just fine.

__sample code__
FOR batch in 1 .. num_batches
LOOP
   OPEN c_cursor

        (
           f_func1( ... batch )
          ,f_func2( ... batch )
         );
   FETCH c_cursor BULK COLLECT INTO
     tab_foos

,tab_bars
,tab_bazs;

   CLOSE c_cursor;

   FORALL j IN tab_foos.FIRST .. tab_foos.LAST

     INSERT
       INTO table_name
            (
              field1
             ,field2
            )
     SELECT ...
      WHERE ...
            ... = tab_foos(j)
            ...;

   tab_foos.DELETE;

   tab_bars.DELETE;
   tab_bazs.DELETE;
END LOOP; Received on Thu Feb 12 2004 - 12:45:39 CST