Home Execute database function with JPA
Reply: 1

Execute database function with JPA

anste
1#
anste Published in 2018-01-11 11:32:38Z

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

Simon Martinelli
2#
Simon Martinelli Reply to 2018-01-11 14:25:48Z

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/

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO