Hi All,
I'm trying to create below procedure to retrieve a data set (cur_a) and update a column based on the data set but I get ORA-01002: fetch out of sequence.
Not sure what I'm missing, please help. Thanks.
v_trx NVARCHAR2 (128);
v_id Integer;
BEGIN
OPEN cur_a FOR
SELECT id1 id1, id2 id2
FROM
(
SELECT id1, id2
FROM table1
WHERE trx1 is null
AND trx2 is null
AND status is null
UNION ALL
SELECT b.id1, b.id2
FROM table1 a, table1 b
WHERE (b.trx1 = a.id2
AND b.trx2 is null)
AND a.status in ('Success','Error')
AND b.status is null
UNION ALL
SELECT d.id1, d.id2
FROM table1 c, table1 d
WHERE d.id2 IN
(
SELECT b.id2
FROM table1 a, table1 b
WHERE (b.trx2 = a.id2)
AND a.status in ('Success','Error')
)
AND (d.trx1 = c.id2)
AND c.status in ('Success','Error')
AND d.status is null
)
WHERE rownum <= 100;
LOOP
FETCH cur_a into v_trx, v_id;
EXIT WHEN cur_a%NOTFOUND;
UPDATE table1
SET status = 'In Progress'
WHERE id2 = v_id;
END LOOP;
END;
/