Transform Cursor to Bulk

From: Diego <dpafumi_at_yahoo.com>
Date: 15 Jul 2004 14:22:16 -0700
Message-ID: <5b2b7013.0407151322.428e27fd@posting.google.com>

HI, I'm trying yo improve the performance of the following piece of code.
Here I'm archiving Items that are done processing to Archive Tables. I believe that if I use BULK INSERTS/SELECTS/UPDATES this process will move much faster.

Can someone suggest me a way to do it? Thanks

declare
  cursor curflowdoc is select docid, state, other, rowid

                        from diego_parent
                        where state = 200;

  show_value varchar2(40);
  v_payee varchar2(40);
begin
  for cur1 in curflowdoc loop

--Archive child items

     insert into diego_child_arch
       select * from diego_child
         where docid = cur1.docid;



     --Archive Parent items 

     insert into diego_parent_arch
       select * from diego_parent
         where rowid = cur1.rowid;

     show_value := to_char(cur1.docid);
     dbms_output.put_line('Deleting ' || show_value);
     


     ---More selects and updates here on other tables here based on

that DOCID
     select payee into v_PAYEE  
       from payee_T 
       where other = cur1.other;
     
     insert into temp(a,b,c) values(cur1.docid, cur1.state, v_payee);
     
     
     


     --Delete Archived child items 

     delete from diego_child
        where docid = cur1.docid;



     --Delete Archived Parent items 

     delete from diego_parent
        where rowid = cur1.rowid;

  end loop;
end; Received on Thu Jul 15 2004 - 16:22:16 CDT