One Cursor? or Two Cursors?

From: Steve <schen_at_prodigy.net>
Date: Sun, 8 Apr 2001 14:28:06 -0400
Message-ID: <9aqad8$2vh2$1@newssvr05-en0.news.prodigy.com>

Can someone offer some ideas?

I have one procedure to do two things: 1) utlsmtp job, 2) update table. I would like to know what is the best way in terms of performance by using one cursor or two cursors?

My original codes are like the followings (before adding update table code):

---

procedures is
....
cursor C1 is
select .......;

begin
  for Process in C1 loop
  ....
  ...
  utlsmtp stuffs
  end loop;
end;

---

So I have "insert into tbl values(.....)" clause to add. If I add it before "end loop;", I am doing so with every row after first Process related utlsmtp jobs. If I have 400 rows of records to be processed by utlsmtp, then following I have 400 rows of data inserted into other tbl one of the time.

Would it be better to have insert table job to be done after all utlsmtp job done? If so, where to insert clause, or to need 2nd cursor? When loop is ended, would C1 be finished?

Thanks for any comment.

Steve Received on Sun Apr 08 2001 - 13:28:06 CDT