Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Collect Query
Bulk Collect Query [message #232342] Fri, 20 April 2007 04:02 Go to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

I recently read a post where Michel Cadot stated
Quote:

Use "BULK COLLECT" operation, you're currently programming like we programmed Oracle7.


for someone who was using a cursor fetch loop.

Now I'm a very recent convert from 7.3 to 9.2 (still spotting the good new things Grin ) and we have a vast number of overnight batch jobs that work off cursor loops (both for loops and cursor fetches) to select the data for processing.

I've used bulk collect myself once for a data load (when converting from one table to another) and I liked it Cool but I'd sort of thought of it as a vehicle for bulk loading Embarassed.

Should I look at re-writing our cursor loops to use the bulk collect operation? It's probably a daft question but I thought I'd briefly tap the collective brain on here. I don't have a problem with the syntax but I just wanted to check it's application and whether there are circumstances I shouldn't think about using it?

Thanks in advance for your advice.

Re: Bulk Collect Query [message #232343 is a reply to message #232342] Fri, 20 April 2007 04:07 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If it is a loop with many cycles, I'd certainly go for a bulk operation. The performance gain of bulk loops becomes larger as the number of cycles increases.

Note however that bulk operations have their limits: within the loop you can only do a single SQL statement (EXECUTE IMMEDIATE is one of them, if I'm not mistaken). I believe it has to do with context switches from PL/SQL to SQL, but that's a bit out of my league Wink.

MHE

[Updated on: Fri, 20 April 2007 04:09]

Report message to a moderator

Re: Bulk Collect Query [message #232345 is a reply to message #232342] Fri, 20 April 2007 04:11 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Thanks for your quick response Maaher. So it's worth me looking into - I'm rewriting one of our programs into PL/SQL (from Pro*C) and that uses a consistantly large cursor loop, I'll give it a try with that.

oops quick posting on my part - I'll look into that further then.

Thanks again.

[Updated on: Fri, 20 April 2007 04:12]

Report message to a moderator

Re: Bulk Collect Query [message #232376 is a reply to message #232345] Fri, 20 April 2007 06:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This might help.

Ross Leishman
Re: Bulk Collect Query [message #232384 is a reply to message #232342] Fri, 20 April 2007 06:37 Go to previous message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
It certainly does, thanks Ross. Can't think why I didn't spot it when looking around Embarassed.
Previous Topic: PL/SQL Procedure to send PDF File as attachment
Next Topic: generate Primary key dynamically
Goto Forum:
  


Current Time: Sat Dec 10 03:10:08 CST 2016

Total time taken to generate the page: 0.08523 seconds