Sure, here’s the translation: 简单的JdbcCall调用函数

huangapple go评论139阅读模式
英文:

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(&quot;NEWIB&quot;)
                .withCatalogName(&quot;PKG_ONLINE_IB_PC_OPERATIONS&quot;)
                .withFunctionName(&quot;GET_RISK_GROUP&quot;);
        SqlParameterSource source = new MapSqlParameterSource().addValue(&quot;P_TAX_NUMBER&quot;, 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 -&gt; {
                    CallableStatement cs = con.prepareCall(&quot;{? = call NEWIB.PKG_ONLINE_IB_PC_OPERATIONS.GET_RISK_GROUP(?)}&quot;);
                    cs.registerOutParameter(1, Types.NVARCHAR); // or whatever type your function returns.
                    // Set your arguments
                    cs.setString(2, taxNumber);
                    return cs;
                },
                (CallableStatementCallback&lt;String&gt;) cs -&gt; {
                    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(&quot;NEWIB&quot;)
                .withCatalogName(&quot;PKG_ONLINE_IB_PC_OPERATIONS&quot;)
                .withFunctionName(&quot;GET_RISK_GROUP&quot;)
				.withoutProcedureColumnMetaDataAccess()
				.declareParameters(new SqlOutParameter(&quot;return&quot;,Types.VARCHAR),
                                   new SqlParameter(&quot;p_tax_number&quot;, Types.VARCHAR));
		
// add value to the paramaters						   
SqlParameterSource parameterMap = new MapSqlParameterSource().addValue(&quot;p_tax_number&quot;, 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 =&#39;GET_RISK_GROUP&#39; 
and    Package_name=&#39;PKG_ONLINE_IB_PC_OPERATIONS&#39;

huangapple
  • 本文由 发表于 2020年9月11日 19:53:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/63846557.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定