ORA-01002: fetch out of sequence while using @transactional

13,992

ORA-01002 is an Oracle error. You have not shown any Oracle code so we have to guess what happens.

This error is usually thrown when you commit across a FOR UPDATE cursor, for example:

SQL> CREATE TABLE TEST (ID NUMBER, c VARCHAR2(10));

Table created

SQL> INSERT INTO TEST VALUES (1, 'a');

1 row inserted

SQL> INSERT INTO TEST VALUES (2, 'b');

1 row inserted

SQL> BEGIN
  2     FOR cc IN (SELECT * FROM TEST FOR UPDATE) LOOP -- FOR UPDATE cursor
  3        UPDATE TEST SET c = UPPER(c) WHERE ID = cc.id;
  4        COMMIT; -- this will invalidate our cursor
  5     END LOOP;
  6  END;
  7  /

ORA-01002: fetch out of sequence
ORA-06512: at line 3

I can imagine that adding @Transactional to a unit of work will make it commit on success/rollback on error. So maybe this code is part of a larger loop using a FOR UPDATE cursor of some sort. When you add @Transactional, it commits each time the method is invoked, thus invalidating the main cursor.

You can also encounter the ORA-01002 if you try to fetch from a cursor after having rolled back some of the changes that would invalidate it:

SQL> DECLARE
  2     CURSOR cc IS SELECT * FROM TEST;
  3     rc cc%ROWTYPE;
  4  BEGIN
  5     UPDATE TEST SET c = 'c' WHERE ID = 2;
  6     OPEN cc;
  7     FETCH cc INTO rc;
  8     -- do other things
  9     ROLLBACK;
 10     FETCH cc INTO rc;
 11  END;
 12  /

ORA-01002: fetch out of sequence
ORA-06512: at line 11

Here our cursor cc is invalidated because we have rolled back some of the changes that affect rows in the cursor. Again this could be caused by adding @Transactional and the method rolling back the transaction while another cursor is still being fetched from.

In conclusion: you should only add @Transactional to units that do a an indivisible amount of work. If the method is a submethod of a bigger transaction, it should not commit/rollback on its own.

Share:
13,992
prabu
Author by

prabu

Updated on November 24, 2022

Comments

  • prabu
    prabu 12 months

    When i try to invoke a procedure from my application got an error saying ORA-01002: fetch out of sequence

    Technologies used :

    • Mybatis 3
    • Spring MVC

    The interesting point here is the error occurs only if i use @Transactional (org.springframework.transaction.annotation.Transactional) annotation for the invoking method in service class. If i remove @Transactional then there is no ORA error.

    I am using @Transactional as i have several DAOs injected into a Service. Please find my code pasted below.

    @Transactional
        public boolean saveavgFlyHrs(AverageFlyingHoursReport averageFlyingHoursReport) throws TransactionDataException {
            String status = null;
            boolean isOk = false;
            if(averageFlyingHoursReportDAO.saveavgFlyHrs(averageFlyingHoursReport)) {
                status = averageFlyingHoursReportDAO.updateCheckEff(averageFlyingHoursReport.getSubFleet());
                logger.debug("OUT_STATUS:"+status);
                if(ConstantStringUtil.SUCCESS.equalsIgnoreCase(status)) {
                    isOk = true;
                } else {
                    isOk = false;
                }
            }
            return isOk;
        }
    

    Anyone pls help me with this.

  • prabu
    prabu about 10 years
    Hi Vincent,Excellent!! you are absolutely right.. Thanks for those useful explanation. I have given commit in my procedure after every update which comes under the scenario 1 explained by you. Do you suggest to remove commit from the procedure or removing @Transactional?
  • Vincent Malgrat
    Vincent Malgrat about 10 years
    @prabu Ideally only the main program should decide when to commit because only it knows what constitutes a transaction. I'm not sure I understand all the subtleties of @Transactional but I think the small methods and submethods should not have this attribute since they are not an independent transaction. Following the same logic, if you have Oracle procedures, they should not commit themselves, they should let the client (here the calling application) decide when/if to commit.
  • prabu
    prabu about 10 years
    Thanks Vincent!! Will implement as suggested by you as the idea looks promising..