CODEWITHSUNDEEP
javaoracle-databasespring-jdbcdto
pronomy
pronomy

Reputation: 214

SimpleJdbcCall call function

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.

Upvotes: 1

Views: 3595

Answers (1)

Sujitmohanty30
Sujitmohanty30

Reputation: 3316

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'

Upvotes: 2

Related Questions