I am trying to call a database stored procedure (Oracle 10g) with JPA 2.1 (Hibernate 4.3.5). The procedure returns the results in the output cursor:
PROCEDURE GET_EMPLOYEE( P_ID_EMP IN VARCHAR2, P_CURSOR OUT TYPES.cursor_type ) AS ...
Java Code:
@NamedStoredProcedureQuery(name = "Employee.findById", resultClasses = Employee.class, procedureName = "GET_EMPLOYEE", parameters = { @StoredProcedureParameter(mode = ParameterMode.IN, name = "P_ID_EMP", type = String.class), @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, name = "P_CURSOR", type = void.class) } )
Call:
StoredProcedureQuery query = em.createNamedStoredProcedureQuery("Employee.findById"); query.setParameter("P_ID_EMP", "ID00001"); List<Employee> employees = query.getResultList();
This exception is thrown:
Exception in thread "main" java.lang.UnsupportedOperationException: org.hibernate.dialect.Oracle10gDialect does not support resultsets via stored procedures at org.hibernate.dialect.Dialect.registerResultSetOutParameter(Dialect.java:1612) at org.hibernate.engine.jdbc.cursor.internal.StandardRefCursorSupport.registerRefCursorParameter(StandardRefCursorSupport.java:94) at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.prepare(AbstractParameterRegistrationImpl.java:298) at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:417) at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:378) at org.hibernate.jpa.internal.StoredProcedureQueryImpl.outputs(StoredProcedureQueryImpl.java:251) at org.hibernate.jpa.internal.StoredProcedureQueryImpl.getResultList(StoredProcedureQueryImpl.java:334)
The same code works fine with Eclipselink 2.5.1 (but cannot use it).
I know that I can get a JDBC connection from entitymanager and use the classic Resultset material to get the results, but I would like to use JPA 2.1 - Hibernate.
Thanks.
hibernate jpa cursor
user2811229
source share