• Status: Open
  • Priority: Medium
  • Security: Public
  • Views: 56
  • Last Modified:

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
0
steve wa
Asked:
steve wa
  • 3
  • 3
  • 2
  • +1
9 Comments
 
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

Join & Write a Comment

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now