2

i'm making a connection to an oracle db. I just solved problem with dependency (into the following url there's the needed code like respository, entity etc.):

spring-data-jpa 1.11.16 stored procedure with cursor

Now i'm facing problem with casting exeption when i call the repository. The repository sent me back a list of ForwardOnlyResultSet and i'm not able to map my results. that's the error:

Cannot cast 'oracle.jdbc.driver.ForwardOnlyResultSet' to 'procedure.entity.PocRegions'

my oracle pl/sql procedure is that:

PROCEDURE PRO_RETURN_REGION(
            id_region IN POC_REGIONS.REGION_ID%TYPE,
            o_cursor OUT SYS_REFCURSOR) is
            BEGIN
            --Opening the cursor to return matched rows
                open o_cursor for
                select *
                from POC_REGIONS
                where POC_REGIONS.REGION_ID = id_region;
    END PRO_RETURN_REGION;

Then i tryed to bot implement the call with jpa calls and spring-data-jpa call:

StoredProcedureQuery query = entityManager.createStoredProcedureQuery( "POC_PKG_GEO.PRO_RETURN_REGION");
        query.registerStoredProcedureParameter("id_region", BigDecimal.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("o_cursor", void.class, ParameterMode.REF_CURSOR);

        query.setParameter("id_region", id);

        query.execute();

        //Contains a forwardonlyreswultset (jpa call)
        Object res = query.getOutputParameterValue("o_cursor");

        //Contains an array of 2 Object not mapped (jpa call)
        List<PocRegions> resultList = query.getResultList();

        //Contains a forwardonlyreswultset (spring-data-jpa call)
        List<PocRegions> region = geoRegionRepo.getRegion(id);

How should I be supposed to convert/map the retrieved value, to my entity?

Thank you

osharko
  • 204
  • 3
  • 19
  • You did not post the code for the stored procedure, but I believe there must be an out parameter to return the query result. In your `@Procedure` declaration, there is no "out parameter". Please, check this post, it might help: https://stackoverflow.com/questions/30578089/how-to-select-entities-by-calling-a-strored-procedure-with-spring-data – Vitor Santos Dec 11 '18 at 17:26
  • 1
    hi, thank for replying. i added the output parameter annotation but nothing changed. the problem is that it's not converting. now i'll post some a try that i made with jpa and spring-data – osharko Dec 11 '18 at 17:37
  • 1
    I added the jpa and spring data calls. jpa allow me to retrieve an array of the two correct, but not mapped, object. or, taking the output parameter, it return me a forwardonlyresultset value like spring-data-jpa – osharko Dec 11 '18 at 17:39
  • 1
    how you resolve your problem? – Az.MaYo Feb 21 '19 at 06:51

0 Answers0