469,643 Members | 1,236 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,643 developers. It's quick & easy.

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 6805
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
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.