Home Execute database function with JPA

# 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.
Processed in 0.349344 second(s) , Gzip On .