Transform Cursor to Bulk

[Diego]

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;

[sybrandb]

dp*****@yahoo.com (Diego) wrote in message news:<5b**************************@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

[Diego]

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

sy******@yahoo.com wrote in message news:<a1**************************@posting.google. com>...

dp*****@yahoo.com (Diego) wrote in message news:<5b**************************@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