Re: Transform Cursor to Bulk

I was not expectin that, so you are wrong. I was expecting suggestions about the best way to do it. Thanks "Senior DBA"

sybrandb_at_yahoo.com wrote in message news:<a1d154f4.0407152345.49f008fa_at_posting.google.com>...
> 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 - 17:59:47 CEST