From oracle-l-bounce@freelists.org Thu May 12 09:00:48 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j4CE0ml0013818 for ; Thu, 12 May 2005 09:00:48 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j4CE0j4Z013748 for ; Thu, 12 May 2005 09:00:46 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C1CAD192756; Thu, 12 May 2005 07:58:01 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 26198-07; Thu, 12 May 2005 07:58:01 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3DF2C19273C; Thu, 12 May 2005 07:58:01 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:from:to:cc:references:subject:date:mime-version:content-type:content-transfer-encoding:x-priority:x-msmail-priority:x-mailer:x-mimeole; b=ufRJimEGs7rZWgLQZg17zux8KYOdN5z4jG3unpbjtCgB/aRIV4WG9S/ItFbS1l7JErSLbDw21lcnTp4w+0Fl8G3gucmZkdb29bVg0k2JOg4Cq0AXteKXMwnFEhnLtWNKIv2vY2eLI4eiRU4lu8nD1FMHMbjA4HzZik9NSOBBMQY= Message-ID: <01fd01c556f1$f6c72300$1a03310a@IBME1D11967173> From: "cichomitiko gmail" To: "Melanie Caffrey" Cc: , References: <01d801c55643$a2b33be0$8900a8c0@IBME1D11967173> <5e30486205051117152fbc627f@mail.gmail.com> <004701c556cb$d1678760$1a03310a@IBME1D11967173> Subject: Re: Forall limit Date: Thu, 12 May 2005 14:56:05 +0200 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527 X-archive-position: 19602 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: cichomitiko@gmail.com Precedence: normal Reply-To: cichomitiko@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.1 required=5.0 tests=AWL autolearn=ham version=2.63 Thank you! May be I have to try something like: FORALL e IN 1..1000 -- bulk-bind first part of varray DELETE PUSH_LIST_DELIVERY WHERE ID_MSG_LIST=t_ID_MSG_LIST(e); FORALL e IN 1001..cont_tot -- bulk-bind the rest of varray DELETE PUSH_LIST_DELIVERY WHERE ID_MSG_LIST=t_ID_MSG_LIST(e); But I have to control the number of recors returned, so for example, if it's 3000, then it should be: FORALL e IN 1..1000 DELETE PUSH_LIST_DELIVERY WHERE ID_MSG_LIST=t_ID_MSG_LIST(e); FORALL e IN 1001..2000 DELETE PUSH_LIST_DELIVERY WHERE ID_MSG_LIST=t_ID_MSG_LIST(e); FORALL e IN 2001..cont_tot -- bulk-bind the rest of varray DELETE PUSH_LIST_DELIVERY WHERE ID_MSG_LIST=t_ID_MSG_LIST(e); Regards Dimitre ----- Original Message ----- From: "Melanie Caffrey" To: Cc: ; Sent: Thursday, May 12, 2005 2:45 PM Subject: Re: Forall limit Hi Dimitre, The "limit" is associated with the "bulk collect". If you try this out, change your syntax (below) around a bit. The "bulk collect" would be placed inside your "forall" (since your forall is your iteration scheme, as it were.) Personally, I haven't tried placing a bulk collect inside a forall, however, you *can* control the number of collection indices that you iterate through with a forall. Using this technique, you may be able to break up your forall into chunks without having to include a bulk collect clause. For example: ... FORALL e IN 1..1000 -- bulk-bind first part of varray DELETE PUSH_LIST_DELIVERY WHERE ID_MSG_LIST=t_ID_MSG_LIST(e); then ... FORALL e IN 1001..2000 -- bulk-bind second part of varray DELETE PUSH_LIST_DELIVERY WHERE ID_MSG_LIST=t_ID_MSG_LIST(e); and so on. The indices interated over in your FORALL statement will match the index values in your collection, t_id_msg_list. HTH, Melanie On 5/12/05, cichomitiko gmail wrote: > Thanks! Is the "bulk collect limit" limits also the following forall? > > Is the "fetch c bulk collect into l_data limit 100 forall i in > 1..l_data.count" means that forall will process 100 at a time, > or only the "bulk collect" will process it 100 at a time and then forall > will process all the rows simultaneously? > > Kind Regards > Dimitre > > > ----- Original Message ----- > From: "Connor McDonald" > To: > Sent: Thursday, May 12, 2005 2:15 AM > Subject: Re: Forall limit > > > Just do it in batches of (say) 1000 > > > > When fetching do 'bulk collect limit 1000'. > > > > hth > > connor > > > > On 5/12/05, cichomitiko gmail wrote: > >> Hi all, > >> environment: > >> Oracle 8.1.7.4.0 > >> OS Solaris 8 > >> We have a procedure that fails with the fallowing error: > >>=20 > >> ORA-04030: out of process memory when trying to allocate bytes (,) > >> ORA-06512: at "PKG_PP", line 822 > >> ORA-04030: out of process memory when trying to allocate bytes (,) > >> ORA-06500: PL/SQL: storage error > >> ORA-06512: at line 4 > >>=20 > >> The procedure is using only FORALL clauses, no BULK COLLECT. > >>=20 > >> It seems that the forall clause is causing the error. How can we limit > >> th= > > e > >> input collection in this case? > >>=20 > >> FORALL e in 1..cont_tot > >> delete PUSH_LIST_DELIVERY where > >> ID_MSG_LIST=3Dt_ID_MSG_LIST(e); > >> end if; > >>=20 > >> Regards > >> Dimitre > >>=20 > >> -- > >> http://www.freelists.org/webpage/oracle-l > >>=20 > > > > > > --=20 > > Connor McDonald > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > > =3D=3D > > email: connor_mcdonald@yahoo.com > > web: http://www.oracledba.co.uk > > > > "Semper in excremento, sole profundum qui variat" > > -- > > http://www.freelists.org/webpage/oracle-l > > -- > http://www.freelists.org/webpage/oracle-l > -- http://www.freelists.org/webpage/oracle-l