Call Store Procedure que retorna cursor

Júlio, bom dia, tudo bom?

Tenho aqui uma suposição que pode te ajudar, apesar de nunca ter mexido com Stored Procedures.

Dei uma olhada em alguns posts na internet e encontrei um que indica que para receber de volta um cursos, você precisa declará-lo também, com query.registerStoredProcedureParameter(n, void.class, ParameterMode.REF_CURSOR);

Outra coisa que deve fazer diferença no seu código é dar o nome adequado às suas variáveis. Ao criar a função no banco de dados, você declarou as variáveis IN com nomes. Você deve utilizar estes mesmos nomes na sua query:

-- banco de dados
CREATE OR REPLACE FUNCTION nomeDaFuncao (
    IN userName varchar,
    IN appCode varchar
    -- continuacao da funcao...

entityManager.createStoredProcedureQuery("nomeDaFuncao")
    .registerStoredProcedureParameter("userName", String.class, ParameterMode.IN)
    .registerStoredProcedureParameter("appCode", String.class, ParameterMode.IN)
    // mais codigo...
    .setParameter("userName", userName)
    .setParameter("appCode", appCode)
    // mais codigo...

Juntando as duas correções, vai ficar algo parecido com o seguinte:

StoredProcedureQuery query = entityManager
    .createStoredProcedureQuery("PACKAGE.StoreProcedureName")
    .registerStoredProcedureParameter("userName", String.class, ParameterMode.IN)
    .registerStoredProcedureParameter("appCode", String.class, ParameterMode.IN)
    .registerStoredProcedureParameter("outro", BigDecimal.class,  ParameterMode.IN)
    .registerStoredProcedureParameter("outro2", BigDecimal.class, ParameterMode.IN)
    .registerStoredProcedureParameter(1, void.class, ParameterMode.REF_CURSOR)
    .setParameter("userName", userName)
    .setParameter("appCode", appCode)
    .setParameter("outro", outro)
    .setParameter("outro2", outro2);

query.execute();
query.getResultList();

Provavelmente será necessário mudar o identificador do parâmetro que representa o cursos, mas é algo nessa linha.

Espero ter ajudado! Um abraço e bons estudos,

Marco

Fala Marco, tudo beleza!!!

Eu vi vários e vários exemplos na internet, a minha impressão é que a maioria copiou este cara aqui: https://vladmihalcea.com/how-to-call-oracle-stored-procedures-and-functions-from-hibernate/

Em relação as suas considerações já havia testado declarando o cursor como parâmetro e de algumas outras formas diferentes, e entendo a questão de nomear os parametros, estava utilizando a estrutura posicional porque realmente estava somente testando.

Consegui fazer funcionar utilizando typed query.

TypedQuery<Role> query = entityManager.createNamedQuery("getRolesTeste", Role.class);
        query.setParameter(0, userName);
        query.setParameter(1, appCode);    
        query.setParameter(2, Types.NULL);
        query.setParameter(3, Types.NULL);

Desta forma funcionou, Obrigado !!

E fica a dúvida no que esta errado no uso do StoredProcedureQuery, ele da erro justamente no momento de registrar o Cursor. Lembrando que estou usando Oracle.

Abs!

Fala Júlio, beleza?

Que loucura. Eu infelizmente não vou poder resolver essa peculiaridade pois não tenho um ambiente pronto pra fazer esses testes aqui, mas acho que o importante é você ter conseguido resolver o problema!

Consegue dar mais detalhes sobre a resolução (mostrar simplificadamente seu procedure, por exemplo, os parâmetros de entrada e saída) e depois marcar sua própria resposta como solução?

Assim você deixa a solução mais clara aqui e ainda por cima marcada pra outros que tiverem a mesma dúvida! E ainda ganha uns pontos extras no ranking!

Um abraço e boa semana, Júlio!

Marco

Beleza, Marco!

Vou descrever aqui até mesmo porque alguém pode dar outra solução.

Meu ambiente é Spring Boot + Weblogic + Oracle.

Meu problema inicial é que tenho esta procedure:

PROCEDURE getRolesTeste(  username    IN  VARCHAR,
                         appCode     IN   VARCHAR,
                         centrosaude IN   NUMBER,
                         idPerfil    IN   NUMBER,
                         resultado OUT SYS_REFCURSOR);

A procedure funciona, consigo fazer o Call utilizando o outras ferramentas e por linha de comando. Quero fazer o Call utilizando o JPA (ou o Spring data).

Com procedures sem o Cursor resultado OUT SYS_REFCURSOR, funciona normalmente. Porém quando tenho o cursor, tentei de várias formas, não consegui fazer o Call da Procedure.

Utilizando Statement e diretamente JDBC com uso de classes como Oracle Types funciona.

Mas quando tento diretamente com JPA, acontece o erro abaixo. parece que o erro é na forma de registrar o Cursor.

Caused by: org.hibernate.exception.GenericJDBCException: Error registering REF_CURSOR parameter [resultado]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
    at org.hibernate.engine.jdbc.cursor.internal.StandardRefCursorSupport.registerRefCursorParameter(StandardRefCursorSupport.java:72)
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.prepare(AbstractParameterRegistrationImpl.java:353)
    at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:444)
    at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:404)
    at org.hibernate.procedure.internal.ProcedureCallImpl.outputs(ProcedureCallImpl.java:663)
    at org.hibernate.procedure.internal.ProcedureCallImpl.getResultList(ProcedureCallImpl.java:751)
    ... 76 common frames omitted
Caused by: java.sql.SQLException: Invalid column type: 2012
    at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3978)
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:140)
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:2401)
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:2307)
    at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1250)
    at weblogic.jdbc.wrapper.CallableStatement_oracle_jdbc_driver_OracleCallableStatementWrapper.registerOutParameter(Unknown Source)
    at org.hibernate.engine.jdbc.cursor.internal.StandardRefCursorSupport.registerRefCursorParameter(StandardRefCursorSupport.java:69)
    ... 81 common frames omitted

A questão é o mapeamento do Cursor Oracle, inclusive o mesmo código com DB2 funciona sem problemas.

Para conseguir fazer funcionar segui o seguinte caminho:

Tranformei a Procedure em Function

FUNCTION getRoles(  username    IN  VARCHAR,
                         appCode     IN   VARCHAR,
                         centrosaude IN   NUMBER default null,
                         idPerfil    IN   NUMBER)RETURN SYS_REFCURSO

Utilizei named native query, para mapear a função e para executar TypedQuery.

@NamedNativeQuery(
       name = "getRoles", 
        callable = true, 
        query = "{? = call NEW_WEBRNU_TESTE.GETROLES(?,?,?,?)}",
        resultClass = Role.class  )

      TypedQuery<Role> query = entityManager.createNamedQuery("getRoles", Role.class);
        query.setParameter(0, userName);
        query.setParameter(1, appCode);    
        query.setParameter(2,centroSaude !=null ?centroSaude:Types.NULL);
        query.setParameter(3, perfil!=null?perfil:Types.NULL);
        List<Role> roles = query.getResultList();

Este último código funciona.

Obs: neste codigo

• query.setParameter(2,centroSaude !=null ?centroSaude:Types.NULL);

o Types.NULL na verdade é o valor ZERO e o banco recebe o zero como parâmetro, mas na verdade quero que o banco receba null. Mas esta será questão para outro tópico.