471,854 Members | 1,592 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,854 software developers and data experts.

Transform Cursor to Bulk

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;
Jul 19 '05 #1
2 6914
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
Jul 19 '05 #2
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

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by robert | last post: by
2 posts views Thread by php newbie | last post: by
reply views Thread by tedlaraghu | last post: by
2 posts views Thread by Diego | last post: by
NeoPa
reply views Thread by NeoPa | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.