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

Answer 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;
        }