CODEWITHSUNDEEP
javastored-proceduresjdbcplsqloracle11g
Aniket Thakur
Aniket Thakur

Reputation: 68915

Incorrect order of binded params in oracle stored proc called from Java using JDBC callable statement

Following is the oracle proc call from java using binded params - 

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;
            }
        }
    );

But it does not seem to work. In the proc when I log values I am getting values of PARAM1 in PARAM2 and that of PARAM2 in PARAM3.

Upvotes: 2

Views: 815

Answers (1)

Aniket Thakur
Aniket Thakur

Reputation: 68915

This looks like an oracle bug to me. As it turns out named parameters (":PARAM1”) are not so named, after all, but the order of setting them does matter. Finally following worked for me - 

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;
            }
        }
    );

Looks like oracle specs also says to avoid setXXX methods -

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.

More details on the issue -> http://info.michael-simons.eu/2012/07/23/oracle-jbdc-callablestatements-and-named-parameters/

Upvotes: 1

Related Questions