Home » SQL & PL/SQL » SQL & PL/SQL » FOR LOOP statement - Pass parameter from a recordset
FOR LOOP statement - Pass parameter from a recordset [message #417826] Tue, 11 August 2009 12:09 Go to next message
alekons
Messages: 8
Registered: July 2009
Junior Member
Hi,

I am trying to create a loop based on values from a record set.
How to pass the i values inside the loop.

Here is the query:
for i in (select country from locales) LOOP
spool spoolfile.txt
	select trim(leading '+' from number)||';'
	from users 
	where number is not null 
	and locale_id=(select locale_id from locales where country='i'); -- HERE I NEED THE PARAMETER
spool off
END LOOP;
END;
/



Thanks
Alexis
Re: FOR LOOP statement - Pass parameter from a recordset [message #417827 is a reply to message #417826] Tue, 11 August 2009 12:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>How to pass the i values inside the loop.
pass them where for what purpose?
Re: FOR LOOP statement - Pass parameter from a recordset [message #417828 is a reply to message #417826] Tue, 11 August 2009 12:15 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
1. spool is a sql*plus command and can be used in sql, not in between pl/sql block.
2. If you want to use pl/sql block, then "select into" needs to be used, not the normal select.
3. Why can't you use an inner select instead of for loop?

By
Vamsi
Re: FOR LOOP statement - Pass parameter from a recordset [message #417829 is a reply to message #417826] Tue, 11 August 2009 12:16 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
First of all, you cannot spool inside a PL/SQL block. You'll need UTL_FILE, which write out to the server, not client.

Is i.country what you are looking for? It is not clear.
Re: FOR LOOP statement - Pass parameter from a recordset [message #417931 is a reply to message #417826] Wed, 12 August 2009 03:18 Go to previous messageGo to next message
alekons
Messages: 8
Registered: July 2009
Junior Member
I have 2 tables Locale (with 5 different locales) and Users where each user belong to a different locale.

i want to execute the select on table users for every locale i have in the locale table.

select ......... where country= 'THE LOCALE VALUE'

And second how can i write the results to a file.

Re: FOR LOOP statement - Pass parameter from a recordset [message #417932 is a reply to message #417826] Wed, 12 August 2009 03:30 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi,

i) Use Collection i.e Array to accumulate the Country Values
ii) Use the For Loop for your main Query and Get the Country Values from collection
iii) Use UTL_File to write into file for the populated recordset by main Query
Re: FOR LOOP statement - Pass parameter from a recordset [message #417935 is a reply to message #417826] Wed, 12 August 2009 03:52 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Arrays are not needed here. Two queries aren't needed either.
The two queries can be merged into a single query that can be used in the FOR LOOP.
Re: FOR LOOP statement - Pass parameter from a recordset [message #417971 is a reply to message #417826] Wed, 12 August 2009 05:43 Go to previous messageGo to next message
alekons
Messages: 8
Registered: July 2009
Junior Member
can you give an example ?
Re: FOR LOOP statement - Pass parameter from a recordset [message #417999 is a reply to message #417971] Wed, 12 August 2009 07:57 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I gave you the answer already, but you can easily search for examples here or in google.
Previous Topic: vaidating the table structure of 2 tables (merged)
Next Topic: function vs procedure
Goto Forum:
  


Current Time: Sat Dec 10 14:31:33 CST 2016

Total time taken to generate the page: 0.10272 seconds