Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01002: fetch out of sequence Need Help!! (8.1.7.0.0)
ORA-01002: fetch out of sequence Need Help!! [message #596060] |
Tue, 17 September 2013 13:57  |
 |
akull
Messages: 46 Registered: July 2012 Location: Argentina
|
Member |
|
|
Hi Experts!
I'm currently facing a non-critical situacion while trying to update a table. Here's the PL/SQL Code. It's a pretty straight-forward script, It just update one single column of the table. The only problem is that I have to update over 15 milions records. When I use the for update cursor, I put inside the loop a counter, when it reaches 1000 records, then commit, else keep counting.
The block throws the exception ORA-01002: fetch out of sequence.
I've tried to do some reserch on Google, buy it only says it is an out of secuence fetch caused by a commit inside a for update cursor.
My question is. Is there any risk by putting the commit under the end loop, I mean by doing this I'd be updating over 15.000.000 records at the same time. Will I have any issue with the rollback segment ?
Here's the code as along with the Create Table and Insert statements.
Thanks in advance as always!
Regards,
Steve!
CREATE TABLE TEST_1
(
TEST_ID NUMBER PRIMARY KEY,
IS_LOCKED NUMBER NOT NULL
);
INSERT INTO TEST_1
(TEST_ID, IS_LOCKED)
VALUES
(1, 0);
INSERT INTO TEST_1
(TEST_ID, IS_LOCKED)
VALUES
(2, 0);
INSERT INTO TEST_1
(TEST_ID, IS_LOCKED)
VALUES
(3, 0);
INSERT INTO TEST_1
(TEST_ID, IS_LOCKED)
VALUES
(4, 0);
INSERT INTO TEST_1
(TEST_ID, IS_LOCKED)
VALUES
(5, 0);
COMMIT;
DECLARE
CURSOR GET_TEST
IS
SELECT TEST_ID
FROM TEST_1
FOR UPDATE;
v_count NUMBER:=0;
BEGIN
FOR i IN GET_TEST
LOOP
UPDATE TEST_1
SET IS_LOCKED = 1
WHERE CURRENT OF GET_TEST;
v_count:= v_count+1;
IF V_COUNT = 2 THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE('Commit Executed');
v_count:=0;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception ' || SQLERRM);
END;
|
|
|
|
|
Re: ORA-01002: fetch out of sequence Need Help!! [message #596063 is a reply to message #596060] |
Tue, 17 September 2013 14:07   |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
akull wrote on Wed, 18 September 2013 00:27
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception ' || SQLERRM);
END;
That's a blunder...A hidden bug. Get rid of it. Else raise the exceptions properly. Have a look WHEN OTHERS
Actually I missed to mention that due to the EXCEPTION WHEN OTHERS, when you execute it in SQL*Plus, you will simply see "PL/SQL procedure successfully completed."
It will throw an error only when you remove the useless WHEN OTHERS. If you put a RAISE, the line number will move to there.
[Updated on: Tue, 04 March 2014 13:02] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu May 29 11:36:25 CDT 2025
|