Oracle: 11gR2
Client: Windows 7
IDE: Toad
Hello,
I am trying to update a table inside a CURSOR FOR UPDATE LOOP using BULK COLLECT LIMIT FORALL. I want to make sure it commits after every iteration of the cursor loop (i.e. after each FORALL finishes). The reason to use FOR UPDATE is to acquire the lock so that nobody else can update those rows, the DBAs want me to COMMIT after a certain number of rows get updated (using as LIMIT) Not surprisingly I am getting "ORA-01002: fetch out of sequence" error.
Question is how do I mitigate it without eliminating CURSOR FOR UPDATE or COMMIT.
--Staging table
CREATE TABLE BAK_tab
(
INDX NUMBER NOT NULL,
ID NUMBER NOT NULL,
COL1 VARCHAR2(48) NOT NULL
);
--Table to be backfilled
CREATE TABLE tab
(
ID NUMBER NOT NULL,
COL1 VARCHAR2(48)
);
--Backfill code
CREATE OR REPLACE PROCEDURE S38336_update_entitlement (start_row_in IN NUMBER, end_row_in IN NUMBER)
IS
CURSOR cur IS
SELECT id, col1 FROM bak_tab
WHERE indx BETWEEN start_row_in AND end_row_in
FOR UPDATE SKIP LOCKED;
TYPE nt_rowid IS TABLE OF cur%ROWTYPE;
vnt_rowid nt_rowid;
BEGIN
-- Get data to process from staging table
OPEN cur;
LOOP
FETCH cur BULK COLLECT INTO vnt_rowid LIMIT 500;
EXIT WHEN cur%NOTFOUND;
-- Update accounts fetched
FORALL i IN 1 .. vnt_rowid.COUNT
UPDATE tab
SET col1 = vnt_rowid(i).col1
WHERE id = vnt_rowid(i).id;
COMMIT;
FORALL i IN 1 .. vnt_rowid.COUNT
UPDATE bak_tab
SET status = 'Complete'
WHERE id = vnt_rowid(i).id;
COMMIT;
END LOOP;
CLOSE cur;
END;
/
Thanks in advance.