英文:
Invalid SQL statement with CallableStatement during Stored proc call
问题
public void setContext(String userId, String someId, List<String> accountsList) {List<SqlParameter> parameters = Collections.singletonList(new SqlInOutParameter("output", Types.VARCHAR));log.info("executing stored procedure with user id: {}", userId);Map<String, Object> t = defaultTemplate.call(connection -> {CallableStatement callableStatement = connection.prepareCall("STORED_PROC(?, ?, ?, ?)");callableStatement.setString(1, userId);callableStatement.registerOutParameter(2, Types.VARCHAR);callableStatement.setString(3, someId);OracleConnection oracleConnection = connection.unwrap(OracleConnection.class);Array array = oracleConnection.createOracleArray("SOME_TYPE", accountsList.toArray());callableStatement.setArray(4, array);callableStatement.execute();return callableStatement;}, parameters);log.info("Status of the stored procedure: {}", t.get("status_output"));}
英文:
I have below code to call a stored procedure with certain parameters.
However it fails on callableStatement.execute(); with the error:
java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
public void setContext(String userId, String someId, List<String> accountsList) {List<SqlParameter> parameters = Collections.singletonList(new SqlInOutParameter("output", Types.VARCHAR));log.info("executing stored procedure with user id: {}", userId);Map<String, Object> t = defaultTemplate.call(connection -> {CallableStatement callableStatement = connection.prepareCall("STORED_PROC(?, ?, ?, ?)");callableStatement.setString(1, userId);callableStatement.registerOutParameter(2, Types.VARCHAR);callableStatement.setString(3, someId);OracleConnection oracleConnection = connection.unwrap(OracleConnection.class);Array array = oracleConnection.createOracleArray("SOME_TYPE", accountsList.toArray());callableStatement.setArray(4, array);callableStatement.execute();return callableStatement;}, parameters);log.info("Status of the stored procedure: {}", t.get("status_output"));}
What am i doing wrong?
答案1
得分: 2
当调用你的存储过程时，只需编写 call 语句：
CallableStatement callableStatement = connection.prepareCall("{call STORED_PROC(?, ?, ?, ?)}");
英文:
Can you just write call statement when calling your procedure
CallableStatement callableStatement = connection.prepareCall("{call STORED_PROC(?, ?, ?, ?)}");
答案2
得分: 1
CallableStatement callableStatement = connection.prepareCall("{call STORED_PROC(?, ?, ?, ?)}");
英文:
CallableStatement callableStatement = connection.prepareCall("STORED_PROC(?, ?, ?, ?)");
Put {} around the argument.And add call before the statement.Like below:
CallableStatement callableStatement = connection.prepareCall("{call STORED_PROC(?, ?, ?, ?)}");
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论