inserting a row after reading from CURSOR

kalukakkad · New User Joined: 10 Mar 2005 Posts: 81

I have a cursor.say TEST_CUR, to select some rows from a table.
Let's say the cursor returns me 3 rows.
Now, after i FETCH from it the first time ,i am inserting the same row into another table.
After this, on FETCHing the 2nd time from TEST_CUR, the query is executed again and i get the same set of rows with the pointer set to 1st row.

Does that mean INSERT operation is causing some problem to the CURSOR.

Pushpak · New User Joined: 06 May 2005 Posts: 18

Hi,

INSERT will never cause your CURSOR activities. This is very common approach, fetch a record & do the updations on the basis of fetched results.

In your case, are you closing a cursor after reading first record, inserting into the different table and before reading a next record.

Pushpak.

kalukakkad · New User Joined: 10 Mar 2005 Posts: 81

Thanks for the reply.
But the current situation is :

I have a cursor.say TEST_CUR, to select some rows from a table.
Let's say the cursor returns me 3 rows.
Now, after i FETCH from it the first time: i do 2 things

1)update some columns of the row fetched
2)and insert the same row into another table.

The cursor is FETCH ONLY as it has joins so can't use FOR UPDATE.

After this, on FETCHing the 2nd time from TEST_CUR, the query is executed again and i get the same set of rows with the pointer set to 1st row.

Is updating the row causing the problem....problem with locs?

EnjoyMF · New User Joined: 27 May 2005 Posts: 88

hi friend

use static cursor...

j_prameela2000 · New User Joined: 01 Jun 2005 Posts: 28 Location: Chennai

Hi,

I hope the cursor should be declared with Update option and in the Update statement we must mention where current of. If I am wrong, please correct me.

khamarutheen · Active Member Joined: 23 Aug 2005 Posts: 677 Location: NJ

hi frnd,
use this
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT...
FOR UPDATE ....
END-EXEC

hope this works