Home » SQL & PL/SQL » SQL & PL/SQL » Bulk collect ..for all..with execute immediate in 8i ???
icon9.gif  Bulk collect ..for all..with execute immediate in 8i ??? [message #251343] Fri, 13 July 2007 01:31 Go to next message
dev01
Messages: 13
Registered: March 2005
Junior Member
Hi Experts,

I'm writing a PL\SQL function to download some of my master files into text files(database version : 8i). I tried following code but it is not supported in 8i it seems. My master files are huge and contains more than 20 millions of data. Does any body has some other method instead of a normal for loop?

here is my code

cursor c1 is
select * from item_master
where ......;

begin
open c1;
fetch c1 bulk collect into var1, var2, var3,.... limit 1000;
for all i in 1..var1.count
execute immediate 'begin write_to_file_proc(:1,:2,:3...) end;' using var1(i), var2(i), var3(i),.... ;
close c1;


end;
Re: Bulk collect ..for all..with execute immediate in 8i ??? [message #251347 is a reply to message #251343] Fri, 13 July 2007 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Then,
It is not supported in any version, it is not valid PL/SQL.
declare
  cursor c1 is
    select * from item_master
    where ......;
begin
  open c1;
  fetch c1 bulk collect into var1, var2, var3,.... limit 1000;
  for i in 1..var1.count loop
    write_to_file_proc(var1(i), var2(i), var3(i),...) ;
  end;
  close c1;
end; 

Regards
Michel
Re: Bulk collect ..for all..with execute immediate in 8i ??? [message #251353 is a reply to message #251347] Fri, 13 July 2007 02:26 Go to previous messageGo to next message
dev01
Messages: 13
Registered: March 2005
Junior Member
ohh..are you sure ? I've tried for all with execute immediate in 10g n its working fine Sad
Re: Bulk collect ..for all..with execute immediate in 8i ??? [message #251357 is a reply to message #251353] Fri, 13 July 2007 03:14 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"execute immediate" will work but it is not the correct way to do it.
The error comes from your "for all".

Regards
Michel

Previous Topic: Error handling in bulk insert/merge
Next Topic: How to select field A having more than one field B with diffrent values ?
Goto Forum:
  


Current Time: Sat Dec 03 13:58:15 CST 2016

Total time taken to generate the page: 0.09293 seconds