java.sql.SQLException:ORA-01002:未按顺序提取 [英] java.sql.SQLException: ORA-01002: fetch out of sequence

问题描述

我有一个演示应用程序来检查选择更新查询

I have one demo application to check select for update query

public class Test implements Runnable{

    public Test() {
    }

    public static void main(String[] args) {
        Thread t1 = new Thread(new Test());
        Thread t2 = new Thread(new Test());

        t1.start();
        t2.start();
    }

    public void run() {
        // TODO Auto-generated method stub
        try{
            String updateWith = "";
            String sel = "SELECT SIDNUMBERTO FROM tblmserialbatchdetail WHERE sidnumberto = ("+
            "SELECT max(sidnumberto) FROM tblmserialbatchdetail WHERE generationmode='A' and nvl(serialprefix,' ') = nvl('',' ') " +
            "and sidlengthwithoutprefix =12) FOR UPDATE";
            //System.out.println("SELECT QUERY ===: "+sel);
            String updatequery = "update tblmserialbatchdetail set sidnumberto = ? where serialbatchid = ?";
            System.out.println();
            Connection connection = Conn.getDBConnection();
            PreparedStatement pStatement = connection.prepareStatement(sel);

            ResultSet rSet = pStatement.executeQuery();

            while(rSet.next()){

                updateWith = rSet.getString(1);
                long value = Long.parseLong(updateWith)+1;
                updateWith = String.valueOf(value);
                System.out.println("resulet To be Updated ===> "+value);
            }
            connection.commit();
            connection.close();



        }catch (Exception e) {
            e.printStackTrace();
        }
    }

}

如果我从选择查询"中删除更新"，则效果很好，否则给我错误

This works fine if i remove For update from select query otherwise give me error

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

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
    at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:943)
    at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2119)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2324)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:421)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:365)
    at com.response.Test.run(Test.java:47)
    at java.lang.Thread.run(Thread.java:595)

推荐答案

SELCT ... FOR UPDATE仅在托管事务的上下文中才有意义，因为它要求对选定的行进行锁定.

SELCT ... FOR UPDATE only makes sense in the context of a managed transaction, since it requires locks to be taken out on the selected rows.

默认情况下，JDBC不使用托管事务，而是使用隐式创建的事务，该事务将在查询执行后立即提交.这将破坏SELECT ... FOR UPDATE的语义，并且JDBC驱动程序会抱怨.

By default, JDBC does not use a managed transaction, it uses an implicitly created one that commits as soon as the query is executed. This will break the semantics of SELECT ... FOR UPDATE, and the JDBC driver complains.

要使用托管交易，请添加

In order to use a managed transaction, add

connection.setAutoCommit(false); 

在执行查询之前.然后，执行connection.commit().

before you execute the query. Afterwards, execute connection.commit().

这篇关于java.sql.SQLException:ORA-01002:未按顺序提取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！