Reputation: 472
I want to call a Oracle function using JPA. I found this thread on this topic.
But my Oracle function only returns a string. Not the result for some kind of entity. I tried something like this:
@NamedNativeQuery(name = "myFuncCall",
resultSetMapping = "myResultSetMapping",
query = "{ ? = call schema.mypkg.somefunc(:id) }",
hints = { @javax.persistence.QueryHint(name = "org.hibernate.callable", value = "true") }
)
@SqlResultSetMapping(name = "myResultSetMapping",
columns = { @ColumnResult(name="somename") }
)
When I call the query like this
Query query = em.createNamedQuery("myFuncCall", String.class).setParameter("id", "42");
String res = (String) query.getSingleResult();
I get
Hibernate: { ? = call schema.somefunc(?) }
18:21:11.222 [main] WARN o.h.util.JDBCExceptionReporter - SQL Error: 6550, SQLState: 65000
18:21:11.222 [main] ERROR o.h.util.JDBCExceptionReporter - ORA-06550: Row 1, Column 13:
PLS-00382: This expression has the wrong type
ORA-06550: Row 1, Column 7:
PL/SQL: Statement ignored
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1389)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1317)
at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:307)
Any ideas?
Upvotes: 4
Views: 4992
Reputation: 47310
I do it a rather hacky way, you have to return a refcursor. So make sure your function returns a ref cursors and does something like below :
result_string := doSoemthing
open l_refcursor for select result_string as str from dual;
return l_refcursor;
then create a "dummy" entity
@Entity
public class NativeString {
private String str;
@Id
@Column(name = "str")
public String getStr() {
return str;
}
public void setStr(String str) {
this.str = str;
}
}
and you can use the native query functionality
@NamedNativeQueries({
@javax.persistence.NamedNativeQuery(name = "whatevs", query = "" +
"{? = call myfunction(:id)}", resultClass = NativeString.class,
hints = {
@javax.persistence.QueryHint(name = "org.hibernate.callable", value = "true")
}
)
})
This is about as ugly as it gets... there is not nice way afaik with hierbnate and stored procedures and functions.
Upvotes: 0
Reputation: 472
We ended up using a CallableStatement
. It works, but tell me if there's a better solution!
String funcCall = "{? = call schema.mypkg.somefunc(?)}";
Connection conn = ((SessionImpl)em.getDelegate()).connection();
CallableStatement stmt = conn.prepareCall(funcCall);
stmt.setInt(2, 42);
stmt.registerOutParameter(1, Types.CHAR);
stmt.executeUpdate();
String result = stmt.getString(1);
Upvotes: 3