Execute database function with JPA

Question

To call an Oracle database function I'd simply write

final Query query = entityManager.createNativeQuery(
    "select myfunction(?) from dual"
);
query.setParameter(1, "value");
Object rv = query.getSingleResult();

This works as long as the called function doesn't execute any dml operations. Otherwise I'd have to execute something like

    {? = call myfunction(?)}

Unfortunately I can't register any OUT parameters, so I can't get this statement to work. How can I achieve that without using plain JDBC?

---

Edit:

The question was misleading. I want to fetch the result of a function (not a stored procedure) in an Oracle database. There are no OUT parameters. The function could look like this:

CREATE OR REPLACE FUNCTION myfunction(value IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
    UPDATE foo SET bar = 'foobar'
    WHERE id = 1;

    RETURN 'test';
END myfunction;

The answer to Calling an oracle function from JPA does not solve my problem since there are dml changes inside my function. I get an error:

cannot perform a DML operation inside a query

Solution 1

• Select Query will not work when there are any DML statements in the function.
  If there are no DML statements then SELECT QUERY is the best and efficient way to proceed.
• If there are DML statements, then you have to go with interface CallableStatement.
  It is pretty forward when we are using JDBC in our project, but for Spring Project i.e. using JPA we will need to obtain the session from EntityManager and ReturningWork.

  Session session = entityManager.unwrap(Session.class);
      CallableStatement callableStatement =  session.doReturningWork(new ReturningWork<CallableStatement>() {
            @Override
            public CallableStatement execute(Connection connection) throws SQLException {
                CallableStatement function = connection.prepareCall(
                        "{ ? = call package_name.function_name(?,?) }");
                function.registerOutParameter(1, Types.INTEGER);
                function.setLong(2, 56);
                function.setString(3,"some text");

                function.execute();
                return function;
            }
        });

        try {
            return callableStatement.getBigDecimal(1);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }

Solution 2

You must use a StoredProcedureQuery:

StoredProcedureQuery query = this.em.createStoredProcedureQuery("myfunction");
query.registerStoredProcedureParameter("inparam", String.class, ParameterMode.IN);
query.registerStoredProcedureParameter("outparam", String.class, ParameterMode.OUT);

// set input parameter
query.setParameter("inparam", "Hello);

// call the stored procedure and get the result
query.execute();
String result = (String) query.getOutputParameterValue("sum");

If you are expecting one or more results you can use

getSingleResult() or getResultList() 

like on the Query interface instead of getOutputParameterValue()

Find more information here: https://www.thoughts-on-java.org/call-stored-procedures-jpa-part-2/

Comments

[anste]

I edited my question. I want to fetch the return value of a function, not any OUT parameter.

[Simon Martinelli]

There is getResultList() and getSingleResult on the Query interface have a look at the docs docs.oracle.com/javaee/7/api/javax/persistence/…

[anste]

Thank you for your help, but I can't get this to work. Hibernate always creates a statement like "{call myfunction(?)}". So a procedure is expected and I get "myfunction is not a procedure or is undefined". Could you give an example?

[coladict]

I believe the query is transformed into "{call myfunction(?)}" when using createStoredProcedureCall or createStoredProcedureQuery. If you need just the return, then just use createNativeQuery.

[anste]

@coladict how would a solution with createNativeQuery look like without using select?

[coladict]

Well, normally you'd just do select myfunction(?), but I just checked and Oracle doesn't allow for select without from. Maybe you could do select * from myfunction(?).

[anste]

SELECT can't be used if the function contains DML operations. Otherwise I would write 'select myfunction(?) from dual'.