COMMIT affect CURSOR operation

Once I had a difficulty during a fetch loop using CURSOR in ORACLE.

In each loop, after fetching the row, I insert it to some other table in the same database. Since this loop (insertion) involved some big number of rows, I want to commit the inserted rows to prevent any rollback from a failed inserting as well as to clear the rollback log which soon be fill up.

Now, the question is, the insertion is occur while the cursor opened (fetching), once I commit the insertion inside the loop, it also close the cursor as a result of commit. Is ther a way to around this.

I do think this is a basic thing in developing of large database appl. which might be discussed over some of the programmers. I could get ride of this by using two connection objects (link), such as a transaction object in PowerBuilder (or other frond end tools), which resolve this from outside database engine. I am seeking a ORACLE way to solve this.

Any advice would be appreciated.

K K
K K

KK     -----------------  E-Mail: yukq_at_bernstein.com
K K
K  K                      Tel. (212)756-1904 (Office)