Hi,

I am using Hibernate 4.3.11 version with JPA implementation to invoke a stored procedure which has OUT paratemer of type SYS_REFCURSOR.

Below is the piced of cide I have written in JPA

@NamedStoredProcedureQuery(name = "ADMIN_TAX_CALC", procedureName = "admintax_pr",
parameters = { @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class, name = "outValue"),
@StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name = "policyNbr"),
@StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name = "repCat1"),
@StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name = "repCat2"),
@StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name = "repCat3"),
@StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name = "repCat4"),
@StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name = "repCat5"),
} )


entityManager = getEntityManager();

StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("ADMIN_TAX_CALC");
query.setParameter("policyNbr", "xxxxx");
query.setParameter("repCat1", "xxxx");
query.setParameter("repCat2", "xx");
query.setParameter("repCat3", "");
query.setParameter("repCat4", "");
query.setParameter("repCat5", "");
query.getResultList();



But this sis returning error " R java.lang.UnsupportedOperationException: org.hibernate.dialect.Oracle9iDialect does not support resultsets via stored procedures
[10/21/15 19:14:38:141 IST] 0000005f SystemErr R at org.hibernate.dialect.Dialect.registerResultSetOutParameter(Dialect.java:1612)
[10/21/15 19:14:38:141 IST] 0000005f SystemErr R at org.hibernate.engine.jdbc.cursor.internal.StandardRefCursorSupport.registerRefCursorParameter(StandardRefCursorSupport.java:94)
"


Any support /help at the earliest will be very useful.

Yes, for Hibernate team, it's hard to support that, If the query is wrote by JPQL or NativeSQL, the metadata of query is visible for Hibernate so that it can do the result mapping easily, but if the SQL is hidden by procedure, Hibernate can't know the metadata of query statement.

It's not a problem in our real projects

(1) Query business often execute one complex SQL to query one ResultSet(JDBC support multiple ResultSets from multiple SQL, but it's very hard to be used in real projects and unfriendly for ORM). For this case with only one SQL statement, SQL and procedure have the same performance(One SQL can be optimized by the SharedCache of database very easily)
(2) Changing business often execute many simple SQLs to modify many data of many tables because the business logic often is complex, that need use the procedure to optimize the performance and Hibernate support it very well.

Thanks for the reply. Is there any other way we can call this stored procedure using hibernate JPA API?. I have tried using NamedNativeQuery also but it is not working. The same procedure is also used in some other project where they use hibernate3 version(without JPA) and it all works fine there without any issue(defining stored procedure in hbm file and then calling in java using session.createNamedQuery)