vendredi 29 mai 2015

How to get cursor result using Spring Jdbc?

All I am new to Spring. I am trying to call store procedure which is returning result in the form of cursor.I tried with rowMapper also even with OracleTypes.CURSOR. But every time I am getting error.I am using Oracle Database. Please guide me. Where I am doing wrong.I tried all the jdbc option to pass the parameter but not able to figure out.

Procedure

create or replace PROCEDURE SELECT_DETAILS
(IN_Logged_User_ID NUMBER)
AS
cursor SELECT_ALL_DATA is
    select
        type,
        type_desc,
        file_format,
        decode(active_flg, 'Y',1,0) as active_flg,
        to_char(modified_date, 'mm/dd/yyyy hh24miss') as modified_date
    from dtt
    order by doc_type;
ERR_MSG varchar2(600);
APP_ERROR exception;
Err_no       NUMBER;
BEGIN
  IF Err_no = -1 THEN
  RAISE APP_ERROR;
     RETURN;
  END IF;

    BufferMgr.ClearBuffer;
    BufferMgr.MaxColumns(6);
    for dttRow in SELECT_ALL_DATA
    loop
        BufferMgr.PutColumn(dttRow.doc_type);
        BufferMgr.PutColumn(dttRow.doc_type);
        BufferMgr.PutColumn(dttRow.doc_type_desc);
        BufferMgr.PutColumn(dttRow.def_file_format);
        BufferMgr.PutColumn(dttRow.active_flg);
        BufferMgr.PutColumn(dttRow.modified_date);
        BufferMgr.NewRow;
    end loop;

    exception
    when APP_ERROR then
        Rollback;
        BufferMgr.PutColumn('!@#$' || ERR_MSG);
        BufferMgr.NewRow;
        RAISE BufferMgr.app_errors_exit;
    when others then
        Rollback;
        BufferMgr.PutColumn('!@#$' || TrackORAError(SQLCODE,SQLERRM));
        BufferMgr.NewRow;
        RAISE BufferMgr.app_errors_exit;
END;

Java

public Map<?, ?> simpleProcedureCall(String procedureName,Long loginId){
        SimpleJdbcCall procReader = new SimpleJdbcCall(jdbcTemplate);
        procReader.withProcedureName(procedureName).declareParameters(new SqlOutParameter("SELECT_ALL_DATA",OracleTypes.CURSOR, new DocTypeMapper()),
                new SqlParameter("IN_Logged_User_ID", Types.NUMERIC));
        SqlParameterSource inParams = new MapSqlParameterSource().addValue("IN_LOGGED_USER_ID", loginId,Types.NUMERIC);
        Map<String, Object> simpleJdbcCallResult =  procReader.execute(inParams);
        return simpleJdbcCallResult;
    }

    private class DocTypeMapper implements RowMapper<DocumentType> 
    {
        @Override
        public DocumentType mapRow(ResultSet rs, int rowNum) throws SQLException {

             DocumentType docTypeObject = new DocumentType();
             docTypeObject.setDocType(rs.getString("type"));
             docTypeObject.setDescription("type_desc");
             docTypeObject.setFileFormat("file_format");
             docTypeObject.setIsActive("active_flg");
             docTypeObject.setLastModified("modified_date");
            return docTypeObject;
        }  

    }

error.

Caused by: org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call SELECT_DETAILS()}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SELECT_DETAILS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1137)
    at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1173)
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:378)
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:341)
    at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:190)
    at com.apple.ist.nfa.shared.dao.ProcedureExecutorDAO.simpleProcedureCall(ProcedureExecutorDAO.java:56)
    at com.apple.ist.nfa.service.controller.impl.DocumentService.getDocumentType(DocumentService.java:51)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:137)
    at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTarget(ResourceMethodInvoker.java:296)
    at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:250)
    at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:237)
    at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:356)
    ... 30 more
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SELECT_DETAILS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Aucun commentaire:

Enregistrer un commentaire