Re: Forall limit

From: Melanie Caffrey <mel.caffrey_at_gmail.com>
Date: Thu, 12 May 2005 08:45:24 -0400
Message-ID: <a490e09f05051205456f9dcfd0@mail.gmail.com>

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=3Dt_ID_MSG_LIST(e);

then

...
FORALL e IN 1001..2000 -- bulk-bind second part of varray

     DELETE PUSH_LIST_DELIVERY
     WHERE ID_MSG_LIST=3Dt_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
=20

On 5/12/05, cichomitiko gmail <cichomitiko_at_gmail.com> wrote:
> Thanks! Is the "bulk collect limit" limits also the following forall?

>=20

> 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?

>=20

> Kind Regards
> Dimitre

>=20
>=20

> ----- Original Message -----
> From: "Connor McDonald" <mcdonald.connor_at_gmail.com>
> To: <oracle-l_at_freelists.org>
> Sent: Thursday, May 12, 2005 2:15 AM
> Subject: Re: Forall limit

>=20


> > Just do it in batches of (say) 1000

> >

> > When fetching do 'bulk collect limit 1000'.

> >

> > hth

> > connor

> >

> > On 5/12/05, cichomitiko gmail <cichomitiko_at_gmail.com> wrote:

> >> Hi all,

> >> environment:

> >> Oracle 8.1.7.4.0

> >> OS Solaris 8

> >> We have a procedure that fails with the fallowing error:

> >>=3D20

> >> 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

> >>=3D20

> >> The procedure is using only FORALL clauses, no BULK COLLECT.

> >>=3D20

> >> It seems that the forall clause is causing the error. How can we limit

> >> th=3D

> > e

> >> input collection in this case?

> >>=3D20

> >>               FORALL e in 1..cont_tot

> >>                  delete PUSH_LIST_DELIVERY where

> >> ID_MSG_LIST=3D3Dt_ID_MSG_LIST(e);

> >>            end if;

> >>=3D20

> >> Regards

> >> Dimitre

> >>=3D20

> >> --

> >> http://www.freelists.org/webpage/oracle-l

> >>=3D20

> >

> >

> > --=3D20

> > Connor McDonald
> > =3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=
=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D
> > =3D3D=3D3D
> > email: connor_mcdonald_at_yahoo.com
> > web: http://www.oracledba.co.uk
> >
> > "Semper in excremento, sole profundum qui variat"
> > --
> > http://www.freelists.org/webpage/oracle-l

>=20

> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 12 2005 - 08:50:15 CDT