Bulk Collect Large union cursor
Posted on 2009-06-30
I've used Bulk collect in the past with small tables or cursors and am familiar with the overall process. I'm using Oracle 11G in W Server 03
The current problem is that I have a cursor (GC$CA) that is defined like:
(Select a.*, b.fd from somefile a, someother b where a.condition = b.condition and a.conditionA = 'U' and a.field1 = b.field1
Select a.*, 1 as fd
from somefile where a.conditionA = 'B' )
The code looks like this:
select count(*) into v$row_counter from somefile
While loop_cnt < v$row_counter
FOR cur$ca in G$CA
loop_cnt:= loop_cnt +1
I wish to convert this to a Bulk collect. My only concern is that the table a has 300+ columns! (inherited code...).
I'm having trouble with
1) defining the collection
I think there's an easier way than to create tables of each of the 300+ fields in the table...?
2) the bulk collect statement.
The statement should be:
Select ?? Bulk Collect into ???
Because of the union and where clauses, I'm confused here.
I'm not intending on cleaning up the tables at this point since that would be a major effort .
I'm trying to take a step at a time and anything that can make this step easier would be greatly appreciated.