当前位置: 动力学知识库 > 问答 > 编程问答 >

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

问题描述:

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.

网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: