1

Based on Callable statement support and many others docs javax.persistence.StoredProcedureQuery is presented in jpa 2.1 to

standardized support for dealing with JDBC CallableStatements (stored procedure and function calls)

but when try to call an oracle function with an instance of StoredProcedureQuery the result always is 

PLS-00221: 'function_name' is not a procedure or is undefined

This code is how i use it:

StoredProcedureQuery query = em.createStoredProcedureQuery("function_name");

query.registerStoredProcedureParameter("param1", String.class, ParameterMode.IN);
query.registerStoredProcedureParameter("param2", String.class, ParameterMode.IN);
query.registerStoredProcedureParameter("param3", String.class, ParameterMode.OUT);
query.setParameter("param1", "will");
query.setParameter("param2", "smith");
query.execute();
Object param3 = query.getOutputParameterValue("param3");

The declaration of my function is like this:

function "function_name"(
      param1 in varchar2, param2 in varchar2, param3 out varchar2
   ) return number  is

So what is the matter with it.

A.v
  • 734
  • 5
  • 26
  • What does the declaration of `function_name` look like? – Codo Dec 29 '14 at 13:08
  • 3
    It seems that it's not possible to call functions using pure JPA. Some people achieve it using a `SELECT F(X) FROM DUAL` query. That won't work in your case as you have an output parameter. Some people revert to native (SQL) statements. In your case, you could rewrite (or wrap) the function as a stored procedure with a second OUT parameter instead of the return value. – Codo Dec 29 '14 at 13:46
  • 1
    As @Codo states, that is really not a good practice to have a function return an out parameter. – OldProgrammer Dec 29 '14 at 14:33
  • The problem is I am using another team functions and they are designed like this and I have to use them like this. Bottom line ,as @Codo said, is wrapping theirs function with my own procedures and call my own procedures. Any way thanks a lot guys. – A.v Dec 29 '14 at 14:44
  • Oracle Function can be accessed through plain JDBC API. check the [solution](https://stackoverflow.com/questions/25649326/calling-oracle-function-with-storedprocedurequery?answertab=active#tab-top). – ukchaudhary Nov 03 '17 at 06:17

0 Answers0