Oracle 在Java呼叫 SELECT...FOR UPDATE的Stored Procedure時，發生java.sql.SQLException: ORA-01002: fetch out of sequence.( ORA-01002:fetch超出序列)錯誤

當在Java的Dao層使用Spring JdbcTemplate呼叫Oracle的預存程序(SP)時，發生java.sql.SQLException: ORA-01002: fetch out of sequence.( ORA-01002:fetch超出序列)錯誤

SP的內容類似如下

PROCEDURE SP_GET_EMPLOYEE (i_id IN NUMBER, o_cur OUT SYS_REFCURSOR) AS
BEGIN

    OPEN o_cur FOR
        SELECT *
        FROM EMPLOYEE
        WHERE ID = i_id
        FOR UPDATE;
    
END SP_GET_EMPLOYEE;

問題原因在於SELECT...FOR UPDATE的輸出的cursor是被鎖定(lock)的，在此情況下如果在交易commit後企圖存取該cursor，則會發生此錯誤。

而jdbcTemplate預設沒有交易管理(transactional management)，因此在執行SP後會立刻commit，可能接著又在Java程式中存取cursor對映的resultSet)而引發錯誤。

解決方法為在呼叫SP的Dao方法加上@Transactional來讓Spring管理此交易。或手動管理交易，在呼叫SP前執行connection.setAutoCommit(false);，存取結束後執行connection.commit()。

參考：

• ORA-01002: fetch out of sequence tips
• java.sql.SQLException: ORA-01002: fetch out of sequence