Howto call Oracle function which returns a string using Hibernate/JPA?

Question

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…

Accepted Answer

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);
```

Howto call Oracle function which returns a string using Hibernate/JPA?

Solution

Problem

Related problems