Home Execute database function with JPA
Reply: 0

Execute database function with JPA

user1245
1#
user1245 Published in May 21, 2018, 11:19 am

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

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.398215 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO