Announcement Announcement Module
Collapse
No announcement yet.
Spring supported Cursor datatype Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Spring supported Cursor datatype

    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?

    [COLOR="rgb(139, 0, 0)"]new SqlOutParameter("o_cursor", OracleTypes.CURSOR);[/COLOR]

    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());

  • #2
    What is the exception? The data type looks fine.

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

    Code:
    procReadActor.returningResultSet("TEST_RESULTSET", new SampleRowMapper());
    and change the cursor out parameter declaration to include the row mapper:

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

    Comment


    • #3
      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)

      Comment


      • #4
        Hi,

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

        Comment

        Working...
        X