CODEWITHSUNDEEP
javaspringstored-proceduresjdbc
arvindk
arvindk

Reputation: 13

Execution of stored function using spring's SimpleJdbcCall not giving output

I am trying to call a stored function using spring's SimpleJdbc call. I have written a simple function which takes two numbers as input and returns their sum. I am using Oracle 11.2g as the database. I am not getting any exceptions but at the same time not getting the result either. The function works well when called from an anonymous PL/SQL block through sql-plus. The code is as follows:

SimpleJdbcCall _simpleJdbcCall=new SimpleJdbcCall(this.jdbcTemplate);
_simpleJdbcCall.withCatalogName("BROADCASTSHEETMANAGEMENT");
_simpleJdbcCall.withSchemaName("PPV");
_simpleJdbcCall.withFunctionName("TEST");
_simpleJdbcCall.withoutProcedureColumnMetaDataAccess();

_simpleJdbcCall.declareParameters(new  SqlParameter("newChangeSequence",java.sql.Types.NUMERIC));
_simpleJdbcCall.declareParameters(new SqlParameter("number1",java.sql.Types.NUMERIC));
_simpleJdbcCall.declareParameters(new SqlParameter("number2",java.sql.Types.NUMERIC));

MapSqlParameterSource mapSqlParameterSource1=new MapSqlParameterSource();
mapSqlParameterSource1.addValue("newChangeSequence", Integer.valueOf(0));
mapSqlParameterSource1.addValue("number1", Integer.valueOf(10));
mapSqlParameterSource1.addValue("number2", Integer.valueOf(20));

    newChangeSequence = _simpleJdbcCall.executeFunction(Integer.class,mapSqlParameterSource1);

System.out.println("Returned changeSequence is: " + newChangeSequence);

The stack trace shows following information:

2013/12/19 18:52:53,604 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - Added declared parameter for [TEST]: newChangeSequence
2013/12/19 18:52:53,604 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - Added declared parameter for [TEST]: number1
2013/12/19 18:52:53,604 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - Added declared parameter for [TEST]: number2
2013/12/19 18:52:53,605 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - JdbcCall call not compiled before execution - invoking compile
2013/12/19 18:52:53,608 [main] - [] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
2013/12/19 18:52:53,609 [main] - [] DEBUG org.springframework.jdbc.datasource.DriverManagerDataSource - Creating new JDBC DriverManager Connection to [jdbc:oracle:thin:@localhost:1521:orcl]
2013/12/19 18:52:53,647 [main] - [] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Registering transaction synchronization for JDBC Connection
2013/12/19 18:52:53,649 [main] - [] DEBUG org.springframework.jdbc.core.metadata.CallMetaDataProviderFactory - Using org.springframework.jdbc.core.metadata.OracleCallMetaDataProvider
2013/12/19 18:52:53,649 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - Compiled stored procedure. Call string is [{? = call PPV.BROADCASTSHEETMANAGEMENT.TEST(?, ?)}]
2013/12/19 18:52:53,649 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - SqlCall for function [TEST] compiled
2013/12/19 18:52:53,651 [main] - [] DEBUG org.springframework.jdbc.core.metadata.CallMetaDataContext - Matching [number2, number1, newChangeSequence] with [number2, newChangeSequence, number1]
2013/12/19 18:52:53,651 [main] - [] DEBUG org.springframework.jdbc.core.metadata.CallMetaDataContext - Found match for [number2, number1, newChangeSequence]
2013/12/19 18:52:53,652 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - The following parameters are used for call {? = call PPV.BROADCASTSHEETMANAGEMENT.TEST(?, ?)} with: {number2=20, number1=10, newChangeSequence=0}
2013/12/19 18:52:53,652 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - 1: newChangeSequence SQL Type 2 Type Name null org.springframework.jdbc.core.SqlParameter
2013/12/19 18:52:53,652 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - 2: number1 SQL Type 2 Type Name null org.springframework.jdbc.core.SqlParameter
2013/12/19 18:52:53,652 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - 3: number2 SQL Type 2 Type Name null org.springframework.jdbc.core.SqlParameter
2013/12/19 18:52:53,653 [main] - [] DEBUG org.springframework.jdbc.core.JdbcTemplate - Calling stored procedure [{? = call PPV.BROADCASTSHEETMANAGEMENT.TEST(?, ?)}]
2013/12/19 18:52:53,655 [main] - [] DEBUG org.springframework.jdbc.core.JdbcTemplate - CallableStatement.execute() returned 'false'
2013/12/19 18:52:53,655 [main] - [] DEBUG org.springframework.jdbc.core.JdbcTemplate - CallableStatement.getUpdateCount() returned -1
Returned changeSequence is: null

The stored procedure code is:

function test(number1 number, number2 number) return number is
    newChangeSequence number(4);
begin
     newChangeSequence:= number1 + number2;
             return newChangeSequence;
end test;

Upvotes: 1

Views: 10346

Answers (1)

nano_nano
nano_nano

Reputation: 12523

you have to use SqlOutParameter like this:

_simpleJdbcCall.declareParameters.declareParameters(new SqlOutParameter("newChangeSequence",java.sql.Types.NUMERIC));

and please post your stored procedure code too.

Upvotes: 1

Related Questions