Bulk collect

due to performance reasons we need to use  bulk collect

1) need to select  data from table 1 (select col1,col2,col3,col4 from table 1 where col3=sysdate)

 fetch through bulk collect


2) need to get the  data from table 2  by passing  the values from above fetch bulk collect


select rowid , col4 from above fetch bulk collect
 as col99  from table2
where  table2.col1=fetch bulk from table1.col1
   and  table2.col2=fetch bulk from table1.col2
and table2.col3=fetch bulk from table1.col3


1  to  ... count     save  exceptions


update table2 set col4=col99  from above select
where  rowid=rowid from  above  select



exceptions
steve waAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
I could not get what is the issue here and what is not working...
do you have any code?

have a look at this

Oracle Bulk Collect tips
http://www.dba-oracle.com/t_oracle_bulk_collect.htm
0
steve waAuthor Commented:
how to pass values from first  bulk fetch to the step 2) select statement or cursor?
0
steve waAuthor Commented:
I need sample code  for the above scenario.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

HainKurtSr. System AnalystCommented:
sample code is in the link

a_store string_array;
a_qty number_array;

cursor c1 is
  select store_key, sum(quantity)
    from sales
   group by store_key;
begin
  open c1;
  fetch c1 bulk collect into a_store, a_qty;
  close c1;
  for indx in a_store.first..a_store.last loop
    dbms_output.put_line(a_store(indx)||'....'||a_qty(indx));
  end loop;
end; /

Open in new window

0
steve waAuthor Commented:
Open cursor and  fetch data using bulk collect is  normal scenario.My scenario is little different

step 1)  open cursor 1 and  fetch data using bulk collect from cursor 1 (  example  table A)
step 2 )  Pass the fetch data from the step 1)  to the   where condition of   cursor 2 or another select statement    and get rowid from the that   ( ex :rowid from table B based on the values from the  table A)
step 3) update Table B based on the rowid   pulled from step 2)
0
HainKurtSr. System AnalystCommented:
logic should be:

1. cursor declaration and opening looping fetching closing is already in that sample...
2. select rowid into v_rowid from tableB where colX=a_ColX(indx)
3. update tableC where rowid=v_rowid
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
declare

  cursor c1 is
    select *
      from table1 a
     where a.whatever = 'foo';

  cursor c2(ci_param1 in varchar2) is
    select *
      from table2 a
     where a.some_column = ci_param1;

begin

  for r1 in c1 loop
  
    for r2 in c2(c1.some_column) loop /* you can also do "open c2(c1.some_column)... and do some bulk collect processing... " */
    
      null; /* do some stuff... */
    
    end loop;
  
  end loop;

end;

Open in new window

0
johnsoneSenior Oracle DBACommented:
Why bulk collect?  Seems like a pretty simple update statement.  Update a table based on a value in another table.  The column names and tables names in the original post are confusing, so I can't write a sample.  Seems like you want to update COL4 in TABLE2, but COL4 came from TABLE1.

This is a total guess, but you should get the idea.
UPDATE table1 t1 
SET    col4 = (SELECT col99 
               FROM   table2 t2 
               WHERE  t1.col1 = t2.col1 
                      AND t1.col2 = t2.col2 
                      AND t1.col3 = t2.col3) 
WHERE  t1.col3 = SYSDATE; 

Open in new window

Writing a single statement into a process where you start to involve PL/SQL probably isn't going to make it faster.
1
Alexander Eßer [Alex140181]Software DeveloperCommented:
Writing a single statement into a process where you start to involve PL/SQL probably isn't going to make it faster.
Yes! No need for PL/SQL if you can do it with pure SQL. The less context switches (PL/SQL engine to SQL engine and vice versa), the better ;-)

You may also use the MERGE for that purpose...
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.