Spring SimpleJdbcCall calling stored procedure without input parameter but with output parameter

Question

I trying to call a stored procedure which only has one out parameter and no input parameter. When I call the stored procedure as shown below, I get "wrong number or types of arguments in call to" error

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(springTemplate) 
.withCatalogName("my_package").withProcedureName("my_procedure")
.withReturnValue().withoutProcedureColumnMetaDataAccess()
.declareParameters(new SqlOutParameter("return", Types.VARCHAR));
 Map out = jdbcCall.execute();
 System.out.println((String)out.get("return"));

I am not sure whats wrong here.I get the error

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; 
bad SQL   grammar [{? = call my_package.my_procedure()}]; nested exception is 
java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'my_procedure'
ORA-06550: line 1, column 7:

Any help will be very much appreciated PL/SQL: Statement ignored

ChrLipp · Accepted Answer · 2013-02-01 08:29:33Z

4

I tried exactly what you did and it worked for me, please post the result of the following statement:

select PACKAGE_NAME, OBJECT_NAME, POSITION, ARGUMENT_NAME, DATA_TYPE, IN_OUT
from USER_ARGUMENTS
where PACKAGE_NAME ='my_package' and OBJECT_NAME = 'my_procedure'
order by PACKAGE_NAME, OBJECT_NAME, POSITION

Update:
I think I found your mistake, quote 'a stored procedure which only has one out parameter and no input parameter'. You have a procedure, not a function. In that case you have to write:

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(springTemplate) 
    .withCatalogName("my_package")
    .withProcedureName("my_procedure")
    .withoutProcedureColumnMetaDataAccess()
    .declareParameters(new SqlOutParameter("YOUR_OUTPARAMETER_NAME", Types.VARCHAR));

edited Feb 1, 2013 at 8:29

answered Jan 31, 2013 at 13:41

ChrLipp

15.7k11 gold badges79 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ryan Over a year ago

Its easy to mix up withProcedureName and withFunctionName

Collectives™ on Stack Overflow

Spring SimpleJdbcCall calling stored procedure without input parameter but with output parameter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related