Oracle Stored Procedure returning REF CURSOR

[sl5337]

I have spent hours trying to make this work and have diligently read through the various posts but I just can't seem to get it. I have been using Spring JDBC for a long time, but not with Oracle stored procedures.

I have a stored procedure that requires one input parameter and contains an output parameter of type REF CURSOR. The procedure itself runs fine when I execute it within my database tool. I have tried various code permutations from the examples in the Spring documentation. Here is the latest:

 SimpleJdbcCall call = this.jdbcCallTemplate.
     withCatalogName("MYSCHEMA.MYPACKAGE").
     withProcedureName("MYPROC").
     useInParameterNames("projectId").
     declareParameters(new SqlParameter("projectId", Types.NUMERIC)).
     returningResultSet("data", new MyBeanMapper());
     
SqlParameterSource parm = new MapSqlParameterSource("projectId", projectId);

return (List) call.execute(parm);

In this code example 'this.jdbcCallTemplate' is an instance of SimpleJdbcCall that was injected into the ancestor class. 'projectId' is a parameter to the dao method call containing this code.

I continue to receive a BadSqlGrammerException indicating 'wrong number or types of arguments in call'. I have tried leaving out the 'useInParameterNames' and 'declareParameters' methods, instead relying on the metadata lookup, but that doesn't make any difference. It seems that Oracle is simply not seeing the input parameter I'm supplying.

Any help is greatly appreciated.

[sl5337]

Even when I remove the input parameter from my stored procedure and from the Java code I still get the same exception. All I'm trying to do now is execute a stored procedure that returns a REF CURSOR with no input parameters and I get 'wrong number or types of arguments'. I just don't get it. Am I supposed to declare an OUT parameter for the REF CURSOR? If so, I don't know what the type would be.

[trisberg]

Try using:

 withSchemaName("MYSCHEMA").
 withCatalogName("MYPACKAGE").
 withProcedureName("MYPROC").

[sl5337]

I saw your suggestion about that in a previous post and tried it but then I received a NullPointerException:

java.lang.NullPointerException
at org.springframework.jdbc.core.metadata.CallMetaDataContext.matchInParameterValuesWithCallParameters(CallMetaDataContext.java:429)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.matchInParameterValuesWithCallParameters(AbstractJdbcCall.java:380)
...

However, this makes me think that at least the input parameter is getting to the stored procedure now. But I don't understand the error. I have double checked to make sure that the parameter name in my Java code exactly matches the parameter name in the stored procedure.

[trisberg]

You shouldn't be getting an NPE either way. Looks like some kind of bug Can you create a JIRA issue and attach a test case - Java and the PL/SQL package.

[rowens]

I have just done something similar to this please see code below:

this.jdbcCall = new SimpleJdbcCall(ds)
				.withSchemaName("schemaName")
				.withCatalogName("packageName")
				.withProcedureName("procedureName")
				.returningResultSet("o_User", ParameterizedBeanPropertyRowMapper.newInstance(MyClass.class))

		SqlParameterSource in = new MapSqlParameterSource().addValue("i_inputParameter", inputParam);

		Map ret = jdbcCall.execute(in);

The only differences really are that I take the injected class and instantiate it as a NEW with the additions and I use the addValue method of the MapSqlParameterSource class. My use of the bean mapper is neither here nor there as you aren't getting to that bit..

Apologies for being basic but do your parameter names match the ones in the stored procedure?