Home » SQL & PL/SQL » SQL & PL/SQL » Issue in Bulk Collect
Issue in Bulk Collect [message #421261] Mon, 07 September 2009 01:15 Go to next message
Uma Raj
Messages: 1
Registered: September 2009
Junior Member
Hi all,

Is it possible to use Bulk collect within a for loop
In the below query , i need to pass the values (header_id, global_name etc) from a cursor , hence i need to use a for loop.

To collect the data pls tell me if I can use BUlk Collect inside the for loop and wht are its limits if i do?.

tell me if we have any other alternatives for this issue, without using for loop and bulk collect inside , but satisfieng the below query


SELECT vs_rev.*, es.salesrep_name sales_agent
FROM (SELECT evgrt.header_id, evgrt.GLOBAL_NAME, evgrt.line_id,
evgrt.order_number, evgrt.salesrep_key,
evgrt.account_class, evgrt.usd_extended_net_revenue,
evgrt.extended_quantity
FROM edw_vsoe_gl_revenue_trx evgrt
WHERE header_id = v_header_id
AND GLOBAL_NAME = v_global_name
AND line_id = v_line_id
GROUP BY evgrt.header_id,
evgrt.GLOBAL_NAME,
evgrt.line_id,
evgrt.order_number,
evgrt.salesrep_key,
evgrt.account_class,
evgrt.usd_extended_net_revenue,
evgrt.extended_quantity
HAVING SUM (evgrt.extended_quantity) > 0) vs_rev,
edw_salesreps es
WHERE es.salesrep_key = vs_rev.salesrep_key
AND es.GLOBAL_NAME = vs_rev.GLOBAL_NAME;
Re: Issue in Bulk Collect [message #421265 is a reply to message #421261] Mon, 07 September 2009 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Mon, 07 September 2009 01:25]

Report message to a moderator

Re: Issue in Bulk Collect [message #421266 is a reply to message #421261] Mon, 07 September 2009 01:26 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
tell me if we have any other alternatives for this issue

Why using PL/SQL to retrieve data?

Regards
Michel
Previous Topic: Problem with annonymous block
Next Topic: Getting the difference of 2 rows
Goto Forum:
  


Current Time: Wed Dec 07 18:26:16 CST 2016

Total time taken to generate the page: 0.06944 seconds