Calling a Oracle Function from JPA 2.1?

Question

Im trying to call an oracle function from JPA 2.1 and am getting this error -

org.hibernate.exception.GenericJDBCException: could not extract ResultSet 
...
Caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 6
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
...

The function is defined -

FUNCTION my_function (
      username        IN   VARCHAR2,
      case_number     IN   NUMBER,
      event_type_id   IN   NUMBER,
      arguments       IN   VARCHAR2,
      note            IN   VARCHAR2
   )
      RETURN NUMBER
   IS
      retval     NUMBER;
   BEGIN
      --  WORKING OF FUNCTION REMOVED
      retval := 1;

      RETURN retval;
END;

And I am calling this through -

@NamedNativeQuery(
        name="my_function",query="{ call proc_history.create_parent_event(:username, :caseNumber, :eventTypeId, :arguments, :note) }"
)
public class MyJPAObject {
//
}

With my DAO -

public Integer callMyFuntion() {

   Query query = em.createNamedQuery("my_function");

   query.setParameter("username", "me");
   query.setParameter("caseNumber", 123456);
   query.setParameter("eventTypeId", 123);
   query.setParameter("arguments", "test");
   query.setParameter("note", "test");

   return (Integer)query.getSingleResult();
}

Ive confirmed I can call the function using -

DECLARE retval NUMBER; 
BEGIN retval := my_function('me', 123456, 123, 'test', 'test'); 
END;

What do I need to do to call this through JPA?

http://stackoverflow.com/questions/3572626/calling-stored-procedure-from-java-jpa — Erich Kitzmueller, May 08 '15 at 15:08
Thanks - Thats for stored procedures - its easier with SP's because they define in and out params — farrellmr, May 08 '15 at 15:09

score 0 · Answer 1 · answered May 12 '17 at 14:03

0

If the functions returns data, it should be like this:

query = "{ ? = call proc_history.create_parent_event(:username, :caseNumber, :eventTypeId, :arguments, :note) }"

answered May 12 '17 at 14:03

nekperu15739

3,311
2
26
25

Calling a Oracle Function from JPA 2.1?

1 Answers1