Re: Transform Cursor to Bulk

dpafumi_at_yahoo.com (Diego) wrote in message news:<5b2b7013.0407151322.428e27fd_at_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;

Sure
Just read the PL/SQL manual on BULK INSERT. This newsgroup is a volunteer operation, you can't expect we are going to develop your code.

Sybrand Bakker
Senior Oracle DBA Received on Fri Jul 16 2004 - 10:11:25 CEST