英文:
SimpleJdbcCall call function
问题
我有一个名为GET_RISK_GROUP的Oracle函数。
当我尝试调用这个函数时:
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withSchemaName("NEWIB")
.withCatalogName("PKG_ONLINE_IB_PC_OPERATIONS")
.withFunctionName("GET_RISK_GROUP");
SqlParameterSource source = new MapSqlParameterSource().addValue("P_TAX_NUMBER", taxNumber);
jdbcCall.executeFunction(String.class, source);
我收到了异常:
2020-09-11 15:40:25.692 ERROR 1276 --- [nio-8698-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call GET_RISK_GROUP()}]; SQL state [99999]; error code [17041]; Missing IN or OUT parameter at index:: 1; nested exception is java.sql.SQLException: Missing IN or OUT parameter at index:: 1] with root cause
Could not found any solution. Any ideas? Because of this problem I changed my code to:
```java
jdbcTemplate.execute(
con -> {
CallableStatement cs = con.prepareCall("{? = call NEWIB.PKG_ONLINE_IB_PC_OPERATIONS.GET_RISK_GROUP(?)}");
cs.registerOutParameter(1, Types.NVARCHAR); // or whatever type your function returns.
// Set your arguments
cs.setString(2, taxNumber);
return cs;
},
(CallableStatementCallback<String>) cs -> {
cs.execute();
String result = cs.getString(1);
return result; // Whatever is returned here is returned from the jdbcTemplate.execute method
}
);
这个代码可以正常工作。
英文:
I have a oracle function named GET_RISK_GROUP.
When I try to call this function:
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withSchemaName("NEWIB")
.withCatalogName("PKG_ONLINE_IB_PC_OPERATIONS")
.withFunctionName("GET_RISK_GROUP");
SqlParameterSource source = new MapSqlParameterSource().addValue("P_TAX_NUMBER", taxNumber);
jdbcCall.executeFunction(String.class, source);
I get exception:
2020-09-11 15:40:25.692 ERROR 1276 --- [nio-8698-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call GET_RISK_GROUP()}]; SQL state [99999]; error code [17041]; Missing IN or OUT parameter at index:: 1; nested exception is java.sql.SQLException: Missing IN or OUT parameter at index:: 1] with root cause
Could not found any solution. Any ideas? Because of this problem I changed my code to:
jdbcTemplate.execute(
con -> {
CallableStatement cs = con.prepareCall("{? = call NEWIB.PKG_ONLINE_IB_PC_OPERATIONS.GET_RISK_GROUP(?)}");
cs.registerOutParameter(1, Types.NVARCHAR); // or whatever type your function returns.
// Set your arguments
cs.setString(2, taxNumber);
return cs;
},
(CallableStatementCallback<String>) cs -> {
cs.execute();
String result = cs.getString(1);
return result; // Whatever is returned here is returned from the jdbcTemplate.execute method
}
);
This works fine.
答案1
得分: 2
基于你的要求,我将为你提供翻译后的内容,只包括代码部分,其他内容都不会包含在内:
With the experience on this I could only say to just make some standard changes and then try because the same code works as you said works in my machine. Don't remember wxactly but in one of SO question it was mentioned the jdbc driver can make difference.
I added `withoutProcedureColumnMetaDataAccess` this because `SimpleJdbcCall` like to query the metadata of the column details very often and to avoid performance problem in future when we have more such calls it is advisable to add it.
For function call we also need to register (how you did with `CallableStatement` or declare it as first parameter which will act as return type for.
I hope below works ,
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withSchemaName("NEWIB")
.withCatalogName("PKG_ONLINE_IB_PC_OPERATIONS")
.withFunctionName("GET_RISK_GROUP")
.withoutProcedureColumnMetaDataAccess()
.declareParameters(new SqlOutParameter("return",Types.VARCHAR),
new SqlParameter("p_tax_number", Types.VARCHAR));
// add value to the paramaters
SqlParameterSource parameterMap = new MapSqlParameterSource().addValue("p_tax_number", taxNumber);
// call
String result = jdbcCall.executeFunction(String.class, source);
P.S.
If doesn't work could you post the function body in the question or run the below query and paste the result here
select object_name,argument_name,position,data_type,data_length,in_out
from user_arguments
where OBJECT_NAME ='GET_RISK_GROUP'
and Package_name='PKG_ONLINE_IB_PC_OPERATIONS'
英文:
With the experience on this I could only say to just make some standard changes and then try because the same code works as you said works in my machine. Don't remember wxactly but in one of SO question it was mentioned the jdbc driver can make difference.
I added withoutProcedureColumnMetaDataAccess
this because SimpleJdbcCall
like to query the metadata of the column details very often and to avoid performance problem in future when we have more such calls it is advisable to add it.
For function call we also need to register (how you did with CallableStatement
or declare it as first parameter which will act as return type for.
I hope below works ,
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withSchemaName("NEWIB")
.withCatalogName("PKG_ONLINE_IB_PC_OPERATIONS")
.withFunctionName("GET_RISK_GROUP")
.withoutProcedureColumnMetaDataAccess()
.declareParameters(new SqlOutParameter("return",Types.VARCHAR),
new SqlParameter("p_tax_number", Types.VARCHAR));
// add value to the paramaters
SqlParameterSource parameterMap = new MapSqlParameterSource().addValue("p_tax_number", taxNumber);
// call
String result = jdbcCall.executeFunction(String.class, source);
P.S.
If doesn't work could you post the function body in the question or run the below query and paste the result here
select object_name,argument_name,position,data_type,data_length,in_out
from user_arguments
where OBJECT_NAME ='GET_RISK_GROUP'
and Package_name='PKG_ONLINE_IB_PC_OPERATIONS'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论