java - 使用 JDBC 可调用语句从 Java 调用的 Oracle 存储过程中绑定(bind)参数的顺序不正确

Question

以下是使用绑定(bind)参数从 java 进行的 oracle proc 调用 -

String GET_TEST_ID = "{call PKG_TEST.prc_gettestid(:PARAM1, :PARAM2, :PARAM3, :OUTPARAM1)}";

String  id = (String)getJdbcTemplate().execute
    (   GET_TEST_ID, new CallableStatementCallback() 
        {
            public Object doInCallableStatement(CallableStatement callableStatement) throws SQLException, DataAccessException 
            {
                callableStatement.registerOutParameter("OUTPARAM1", java.sql.Types.VARCHAR);
                callableStatement.setLong("PARAM1", param1);
                callableStatement.setLong("PARAM2", param2);
                callableStatement.setLong("PARAM3", param3);
                callableStatement.execute();
                String testId = callableStatement.getString(OUTPARAM1); 
                return testId;
            }
        }
    );  

但是好像不行。在过程中，当我记录值时，我在 PARAM2 中获取 PARAM1 的值，在 PARAM3 中获取 PARAM2 的值。

Answer 1

对我来说，这看起来像是一个 Oracle 错误。事实证明，命名参数(“:PARAM1”)毕竟不是这么命名的，但设置它们的顺序确实很重要。最后以下对我有用 -

String  id = (String)getJdbcTemplate().execute
    (   GET_TEST_ID, new CallableStatementCallback() 
        {
            public Object doInCallableStatement(CallableStatement callableStatement) throws SQLException, DataAccessException 
            {
                callableStatement.setLong("PARAM1", param1);
                callableStatement.setLong("PARAM2", param2);
                callableStatement.setLong("PARAM3", param3);
                callableStatement.registerOutParameter("OUTPARAM1", java.sql.Types.VARCHAR); //register last as it is 4th in proc argument
                callableStatement.execute();
                String testId = callableStatement.getString(OUTPARAM1);
                return testId;
            }
        }
    );  

看起来像oracle specs还说要避免 setXXX 方法 -

Binding by name is not supported when using the setXXX methods. Under certain circumstances, previous versions of Oracle JDBC drivers have allowed binding statement variables by name when using the setXXX methods.

有关该问题的更多详细信息 -> http://info.michael-simons.eu/2012/07/23/oracle-jbdc-callablestatements-and-named-parameters/