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 Go to next message
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 #596061 is a reply to message #596060] Tue, 17 September 2013 14:01 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
Get rid of COMMIT inside the loop. You can't commit while fetching from "FOR UPDATE" cursor.

SY.
- Re: ORA-01002: fetch out of sequence Need Help!! [message #596062 is a reply to message #596061] Tue, 17 September 2013 14:07 Go to previous messageGo to next message
akull
Messages: 46
Registered: July 2012
Location: Argentina
Member
Hi Salomon Yakobson,
Thank you for the response. As I said before, if I do that I'd be updating 15 milions records at once.
We had in the past a issue due to the rollback segment, it had run out of memory. Bottom line. We had to restart the database to get it working again.

By doing that, Will I have the same issue again ? we're not talking to a few records, there's milions of them.

Thank you again!
- Re: ORA-01002: fetch out of sequence Need Help!! [message #596063 is a reply to message #596060] Tue, 17 September 2013 14:07 Go to previous messageGo to next message
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

- Re: ORA-01002: fetch out of sequence Need Help!! [message #596066 is a reply to message #596063] Tue, 17 September 2013 14:23 Go to previous messageGo to next message
akull
Messages: 46
Registered: July 2012
Location: Argentina
Member
Sir, thank you for the response again!

I'm not talking about the exception hangling I know where the error comes from, I just put it there as example! I really do know that the error is ORA-01002. I also know how to solve it. as you can see if I remove the counter and the commit, the block is executed normally. My main question is if I remove the counter that commits every 2 rows. What kind of problems I will have. Since I'll be updating a 15 milions records.

Thanks.
- Re: ORA-01002: fetch out of sequence Need Help!! [message #596067 is a reply to message #596062] Tue, 17 September 2013 14:28 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
akull wrote on Tue, 17 September 2013 15:07
By doing that, Will I have the same issue again ?


Yes, you will get it again and again until you get rid of COMMIT. Why are you using FOR UPDATE?


SY.
- Re: ORA-01002: fetch out of sequence Need Help!! [message #596068 is a reply to message #596066] Tue, 17 September 2013 14:30 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
One option is DDL instead of DML. But, it cannot be used everywhere. Still have a look at this T.Kyte's link How to Update millions or records in a table

Come back if you are not satisfied.
- Re: ORA-01002: fetch out of sequence Need Help!! [message #596069 is a reply to message #596068] Tue, 17 September 2013 14:32 Go to previous messageGo to next message
akull
Messages: 46
Registered: July 2012
Location: Argentina
Member
Ok, Thank you for your time!!
- Re: ORA-01002: fetch out of sequence Need Help!! [message #596070 is a reply to message #596069] Tue, 17 September 2013 14:35 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Another good one by Jonathan Lewis Big Update

CTAS (create table as select) is a good option.
- Re: ORA-01002: fetch out of sequence Need Help!! [message #596074 is a reply to message #596069] Tue, 17 September 2013 15:48 Go to previous message
akull
Messages: 46
Registered: July 2012
Location: Argentina
Member
yes! oracle 8i.

SQL> select * from v$version
  2  ;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE    8.1.7.0.0       Production
TNS for Solaris: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

SQL> 
Previous Topic: Parent child relation
Next Topic: how to use lock stmnt in oracle
Goto Forum:
  


Current Time: Thu May 29 11:36:25 CDT 2025