Home » SQL & PL/SQL » SQL & PL/SQL » Forall statement (10.2.0.3.0)
Forall statement [message #347128] Wed, 10 September 2008 17:28 Go to next message
lisrobin
Messages: 2
Registered: September 2008
Junior Member
Question regarding the forall statement.

Can you put a forall statement inside an IF statement?

I want to check for certain table names and do something like:

if lower(in_table_name) IN ('table1','table2','table3') then
forall i in arr_rowids.first .. arr_rowids.last
insert into temp_table
select * from &1 where rowid=chartorowid(arr_rowids(i));
end if;

I'm running into the issue that it is executing the forall statement for every table passed in, not just the tables specified in the IF statement.

I'm using Oracle 10g

Thanks.

[Updated on: Wed, 10 September 2008 17:29]

Report message to a moderator

Re: Forall statement [message #347129 is a reply to message #347128] Wed, 10 September 2008 17:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

So many errors & so little knowledge or understanding.

The PL/SQL Reference Manual can be found at http://tahiti.oracle.com

I suggest you RTFM, to create valid syntax code.
Re: Forall statement [message #347130 is a reply to message #347128] Wed, 10 September 2008 17:34 Go to previous messageGo to next message
lisrobin
Messages: 2
Registered: September 2008
Junior Member
Wow, wasn't expecting such a nasty response.

[Updated on: Wed, 10 September 2008 17:39]

Report message to a moderator

Re: Forall statement [message #347196 is a reply to message #347128] Thu, 11 September 2008 00:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I guess you have either simplified your code or you are interpreting your results wrongly.
Statements withing if-then constructs do NOT get executed if the if-conditions are not met.
Re: Forall statement [message #347201 is a reply to message #347130] Thu, 11 September 2008 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
lisrobin wrote on Thu, 11 September 2008 00:34
Wow, wasn't expecting such a nasty response.

Maybe but you have to follow rules and there are so many errors in your (simplified?) code then it is not possible to answer completly or just give you the short one: yes, you can put forall inside an if statement.

Regards
Michel

Re: Forall statement [message #347314 is a reply to message #347201] Thu, 11 September 2008 05:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be fair, I think Anacedents response was unjustified.

The Op was aking a question and providing a section of code as an example of what he was trying to do.
Whether the code is valid or not has no impact on the answer to the question. It could add riders to the answer - 'Yes, you can put FORALL statements inside IF..END IF; but your code won't work for the following reasons...' but the Yes part of the answer is code-independent.

Ana's answer seems to be part of an increasing tendency round here to criticise and correct Opening Posts before or instead of providing useful answers, in cases where answers could be quite easily provided.
Re: Forall statement [message #347450 is a reply to message #347128] Thu, 11 September 2008 12:28 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi, try checking the variable in your "lower()" statement with the values in the IN list, maybe they really do match up.
Re: Forall statement [message #347581 is a reply to message #347201] Fri, 12 September 2008 04:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Maybe but you have to follow rules


We don't have rules here.
There are guidelines, but theres a very large difference between the two concepts.
Re: Forall statement [message #347583 is a reply to message #347581] Fri, 12 September 2008 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
We don't have rules here.

So spamming is allowed, for instance. Twisted Evil

Regards
Michel
Re: Forall statement [message #347588 is a reply to message #347583] Fri, 12 September 2008 04:43 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Yes, in a way you could say it is. On the other hand removing offending posts is also allowed, so spam-posts are removed.
Previous Topic: Running for multiple countries
Next Topic: send a mail from pl/sql
Goto Forum:
  


Current Time: Sat Dec 10 13:06:11 CST 2016

Total time taken to generate the page: 0.16367 seconds