Spring supported Cursor datatype

[venkythaniks]

Hi,
I wanted to call an oracle Stored procedure. One of the out parameter is a cursor. I am receiving an error for the below statement. Please let me know what datatype I have to specify for the Oracle Cursor type?

new SqlOutParameter("o_cursor", OracleTypes.CURSOR);

SimpleJdbcCall procReadActor = new SimpleJdbcCall(oracleCIHDatasource);
procReadActor.withProcedureName("oracle_sp");
procReadActor.setSchemaName("oracleschema");
procReadActor.withoutProcedureColumnMetaDataAccess ();
procReadActor.declareParameters(new SqlParameter("i_act_src_cd", Types.VARCHAR),//1
new SqlParameter("i_end_dt_ch", Types.TIMESTAMP),//2
new SqlParameter("i_limit_no", Types.INTEGER),//3
new SqlParameter("i_request_id", Types.VARCHAR),//4
new SqlParameter("i_perform_id", Types.VARCHAR),//5
new SqlParameter("i_cust_party_id", Types.VARCHAR),//6
new SqlParameter("i_cont_no", Types.VARCHAR),//7
new SqlParameter("i_inter_id", Types.VARCHAR),//8
new SqlParameter("i_cnt_chan_cd", Types.VARCHAR),//9
new SqlParameter("i_start_dt_ch", Types.TIMESTAMP),//10
new SqlParameter("i_act_code", Types.VARCHAR),//11
new SqlParameter("i_sub_act_cd", Types.VARCHAR),//12
new SqlParameter("i_act_st_cd", Types.VARCHAR),//13
new SqlParameter("i_act_sub_st_cd", Types.VARCHAR),//14
new SqlOutParameter("o_resp_cd", Types.INTEGER),
new SqlOutParameter("o_resp_msg", Types.LONGVARCHAR),
new SqlOutParameter("o_cursor", OracleTypes.CURSOR)
);
MapSqlParameterSource inputParams = new MapSqlParameterSource();
inputParams.addValue("i_act_src_cd", "ALL");//1
java.sql.Timestamp tmStmp = java.sql.Timestamp.valueOf("2010-06-03 16:47:25");
inputParams.addValue("i_end_dt_ch", tmStmp);//2
inputParams.addValue("i_limit_no", Integer.valueOf(10));//3
inputParams.addValue("i_request_id", "");//4
inputParams.addValue("i_perform_id", "");//5
inputParams.addValue("i_cust_party_id", "");//6
inputParams.addValue("i_cont_no", "");//7
inputParams.addValue("i_inter_id", "");//8
inputParams.addValue("i_cnt_chan_cd", "ALL");//9

inputParams.addValue("i_start_dt_ch", null);//10
inputParams.addValue("i_act_code", "");//11
inputParams.addValue("i_sub_act_cd", "");//12
inputParams.addValue("i_act_st_cd", "");//13
inputParams.addValue("i_act_sub_st_cd", "");//14
procReadActor.returningResultSet("TEST_RESULTSET", new SampleRowMapper());

[trisberg]

What is the exception? The data type looks fine.

Since you are declaring the parameters explicitly I would try removing this line:


procReadActor.returningResultSet("TEST_RESULTSET", new SampleRowMapper());

and change the cursor out parameter declaration to include the row mapper:


new SqlOutParameter("o_cursor", OracleTypes.CURSOR, new SampleRowMapper())

[venkythaniks]

Hi,

Thanks for the reply. I tried that and I am getting the same exception. Here is the stack trace.


SystemErr org.springframework.jdbc.UncategorizedSQLException : CallableStatementCallback; uncategorized SQLException for SQL [{call CIH.CIH_GET_ACTIVITY(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; SQL state [null]; error code [-99999]; Unrecognized jdbc type -10; nested exception is com.ibm.db2.jcc.b.SqlException: Unrecognized jdbc type -10
[2/28/11 12:43:43:954 EST] 00000028 SystemErr R at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:83)
[2/28/11 12:43:43:954 EST] 00000028 SystemErr R at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:80)
[2/28/11 12:43:43:954 EST] 00000028 SystemErr R at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:969)
[2/28/11 12:43:43:954 EST] 00000028 SystemErr R at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:1003)
[2/28/11 12:43:43:954 EST] 00000028 SystemErr R at org.springframework.jdbc.core.simple.AbstractJdbcC all.executeCallInternal(AbstractJdbcCall.java:391)
[2/28/11 12:43:43:954 EST] 00000028 SystemErr R at org.springframework.jdbc.core.simple.AbstractJdbcC all.doExecute(AbstractJdbcCall.java:354)
[2/28/11 12:43:43:954 EST] 00000028 SystemErr R at org.springframework.jdbc.core.simple.SimpleJdbcCal l.execute(SimpleJdbcCall.java:181)
[2/28/11 12:43:43:954 EST] 00000028 SystemErr R at com.axa.insurance.dao.JdbcProductDao.invokeCIH_GET _ACTIVITY(JdbcProductDao.java)
[2/28/11 12:43:43:954 EST] 00000028 SystemErr R at com.axa.insurance.dao.JdbcProductDaoTests.testGetP roductList(JdbcProductDaoTests.java:29)
[2/28/11 12:43:43:954 EST] 00000028 SystemErr R at com.axa.insurance.bizobject.ProducerSearchLifeBO.s earchProducer(ProducerSearchLifeBO.java:251)
[2/28/11 12:43:43:954 EST] 00000028 SystemErr R at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
[2/28/11 12:43:43:954 EST] 00000028 SystemErr R at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:64)
[2/28/11 12:43:43:954 EST] 00000028 SystemErr R at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:43)
[2/28/11 12:43:43:970 EST] 00000028 SystemErr R at java.lang.reflect.Method.invoke(Method.java:615)
[2/28/11 12:43:43:970 EST] 00000028 SystemErr R at com.axa.insurance.eis.util.EISWebServicesControlle r.invokeBusinessObject(EISWebServicesController.ja va:373)
[2/28/11 12:43:43:970 EST] 00000028 SystemErr R at com.axa.insurance.producersearch.webservice.Produc erSearchWSImpl.searchDistributor(ProducerSearchWSI mpl.java:61)
[2/28/11 12:43:43:970 EST] 00000028 SystemErr R at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
[2/28/11 12:43:43:970 EST] 00000028 SystemErr R at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:64)
[2/28/11 12:43:43:970 EST] 00000028 SystemErr R at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:43)
[2/28/11 12:43:43:970 EST] 00000028 SystemErr R at java.lang.reflect.Method.invoke(Method.java:615)
[2/28/11 12:43:43:970 EST] 00000028 SystemErr R at com.ibm.ws.webservices.engine.dispatchers.java.Jav aDispatcher.invokeMethod(JavaDispatcher.java:178)

[venkythaniks]

Hi,

I am using the spring 3.0.5 jars. Just thought this info might be useful.