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

Possible duplicate of [Calling an oracle function from JPA](https://stackoverflow.com/questions/5688152/calling-an-oracle-function-from-jpa) — Flown, Jan 11 '18 at 11:39
@anste How did you got it resolved. I am stuck in same issue — Ismail, Jul 18 '19 at 09:36
@Ismail Unfortunately I didn't get this resolved. I changed the functions to procedures with an OUT-parameter where possible. Alternatively you could create a wrapper procedure, that calls the function and puts the result into an out-parameter. — anste, Jul 18 '19 at 09:50
@anste I got it resolved. I didnt find any solution using JPA so I had to use JDBC CallableStatement. I will add it to the Answer — Ismail, Jul 18 '19 at 13:03

Ismail · Accepted Answer · 2019-07-19T03:36:20.850

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

Simon Martinelli · Answer 2 · 2018-01-11T14:25:48.360

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/

edited Jan 11 '18 at 14:25

answered Jan 11 '18 at 11:43

Simon Martinelli

34,053
5
48
82

I edited my question. I want to fetch the return value of a function, not any OUT parameter. – anste Jan 11 '18 at 13:30
There is getResultList() and getSingleResult on the Query interface have a look at the docs https://docs.oracle.com/javaee/7/api/javax/persistence/StoredProcedureQuery.html – Simon Martinelli Jan 11 '18 at 14:24
1

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? – anste Jan 11 '18 at 15:35
I believe the query is transformed into `"{call myfunction(?)}"` when using `createStoredProcedureCall` or `createStoredProcedureQuery`. If you need just the return, then just use `createNativeQuery`. – coladict Jan 11 '18 at 15:46
@coladict how would a solution with createNativeQuery look like without using _select_? – anste Jan 11 '18 at 17:02
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(?)`. – coladict Jan 11 '18 at 21:15
1

SELECT can't be used if the function contains DML operations. Otherwise I would write 'select myfunction(?) from dual'. – anste Jan 12 '18 at 08:42

Execute database function with JPA

2 Answers2