Re: Forall limit
- From: "cichomitiko gmail" <cichomitiko@xxxxxxxxx>
- To: "Melanie Caffrey" <mel.caffrey@xxxxxxxxx>
- Date: Thu, 12 May 2005 14:56:05 +0200
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" <mel.caffrey@xxxxxxxxx>
To: <cichomitiko@xxxxxxxxx>
Cc: <mcdonald.connor@xxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
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 <cichomitiko@xxxxxxxxx> 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" <mcdonald.connor@xxxxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> 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 <cichomitiko@xxxxxxxxx> 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
> >> --
> >> //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@xxxxxxxxx
> > web: http://www.oracledba.co.uk
> >
> > "Semper in excremento, sole profundum qui variat"
> > --
> > //www.freelists.org/webpage/oracle-l
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
