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 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?

you ever find anything good for this, I have hacked soemthing else together as well — NimChimpsky
– NimChimpsky, Commented Aug 21, 2015 at 13:00

Stefan · Accepted Answer · 2013-11-22 09:10:47Z

3

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

edited Nov 22, 2013 at 9:10

answered Nov 22, 2013 at 9:03

Stefan

4825 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

NimChimpsky · Accepted Answer · 2015-08-21 12:59:33Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related