Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Binding -FORALL
Bulk Binding -FORALL [message #200002] Fri, 27 October 2006 06:29 Go to next message
vidya_plsql
Messages: 3
Registered: October 2006
Junior Member
hi ,

I have a query regarding use of FORALL.
Can I have a IF statement after a FORALL?
ie
FORALL j in <first> ..<last>
if <cond> THEN
UPDATE....
end if;
IS this possible? or am i not supposed to use anything other than insert/update/delete (like IF) inside FORALL?

Regards,
Vidhya
Re: Bulk Binding -FORALL [message #200006 is a reply to message #200002] Fri, 27 October 2006 06:45 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You can use the same condition in where clause of the UPDATE.

By
Vamsi
Re: Bulk Binding -FORALL [message #200014 is a reply to message #200006] Fri, 27 October 2006 07:19 Go to previous messageGo to next message
vidya_plsql
Messages: 3
Registered: October 2006
Junior Member
Thanks for tht option. But I dont want to do that due to performance issue. If I am going to have it in my where clause, there wud be unnecessary context switching. This condition that I am checking is in a variable tht is not a column in the table tht i am updating.

When I run the code I have given above, I get an error saying PLS00103, encountered 'IF' when expecting any one of the following......

My doubt is whether IF statement is allowed within a FORALL.

Re: Bulk Binding -FORALL [message #200017 is a reply to message #200014] Fri, 27 October 2006 07:23 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Nope you cant
Re: Bulk Binding -FORALL [message #200023 is a reply to message #200017] Fri, 27 October 2006 07:54 Go to previous messageGo to next message
vidya_plsql
Messages: 3
Registered: October 2006
Junior Member
Thanks.
Re: Bulk Binding -FORALL [message #200254 is a reply to message #200017] Mon, 30 October 2006 01:37 Go to previous messageGo to next message
prabhuapps
Messages: 79
Registered: June 2005
Location: Bangalore
Member
after FORALL we can use only the following statements
INSERT
UPDATE
DELETE

Friends, Please correct me if i am wrong...

Regards,
Prabhu
Re: Bulk Binding -FORALL [message #200255 is a reply to message #200254] Mon, 30 October 2006 01:42 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Go through below link
http://psoug.org/reference/bulk_collect.html
hope this helps;
Previous Topic: Low performance with group by
Next Topic: Callling oracle func with ADO
Goto Forum:
  


Current Time: Sun Dec 04 16:43:29 CST 2016

Total time taken to generate the page: 0.11337 seconds