How to call complex Stored Function using Entity Manager?

Question

I am trying to call this using JPA/Hibernate EntityManager bu I keep getting errors. Essentially this is a stipped-down version of a PL/SQL function.

 FUNCTION create_foo(f_name      VARCHAR2,
                     l_name      VARCHAR2,
                     someNum     NUMBER,
                     someResult  OUT VARCHAR) RETURN NUMBER AS
    id  NUMBER;
    someString VARCHAR2(100);

  BEGIN
    id := foo.generateID(webpassword);
    someResult := create_Result(to_char(aidm)
    -- Some other logic and stuff with someString and id
    
    RETURN id;
  END;

Here's what my code looks like:

 StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery("schema.create_foo")
                    .registerStoredProcedureParameter("f_name", String.class, ParameterMode.IN)
                    .registerStoredProcedureParameter("l_name", String.class, ParameterMode.IN)
                    .registerStoredProcedureParameter("someNum", int.class, ParameterMode.IN)
                    .registerStoredProcedureParameter("someResult", String.class, ParameterMode.OUT)
                    .registerStoredProcedureParameter("return", int.class, ParameterMode.OUT)
                    .setParameter("f_name", "John")
                    .setParameter("l_name", "Doe")
                    .setParameter("someNum", 0);

            Object singleResult = storedProcedureQuery.execute();

I have tried multiple things but I can not get it to work. I have tried changing the return ParameterMode to REF_CURSOR but I got the same error PLS-00306: wrong number or types of arguments in call to 'create_foo'

If I try this code like this without registerStoredProcedureParameter for the return:

 StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery("schema.create_foo")
                    .registerStoredProcedureParameter("f_name", String.class, ParameterMode.IN)
                    .registerStoredProcedureParameter("l_name", String.class, ParameterMode.IN)
                    .registerStoredProcedureParameter("someNum", int.class, ParameterMode.IN)
                    .registerStoredProcedureParameter("someResult", String.class, ParameterMode.OUT)
                    .setParameter("f_name", "John")
                    .setParameter("l_name", "Doe")
                    .setParameter("someNum", 0);

            Object singleResult = storedProcedureQuery.execute();

I get this error: PLS-00221: 'create_foo' is not a procedure or is undefined. I have also tried different combinations of storedProcedureQuery.getSingleResult() and storedProcedureQuery.excecute(). I am using this same process for other stored procedures and they are working great. The only difference is that this one is a function and has an OUT Pram and a RETURN whereas the other ones only have an IN param. This stored procedure/function is being used by other projects and is working so I know the problem is not the PL/SQL but rather my code.

How do I get this to work?

Is your database set to support `NUMBER` and `VARCHAR2`? The first exception says that datatypes may be incorrect. In PL/SQL, the `NUMBER` and `VARCHAR2` data types must be turned on in the database configuration parameter. — tbatch, Nov 17 '22 at 18:31
@skidwiz Yes the database supports ```NUMBER``` and ```VARCHAR2``` — Brian Akumah, Nov 23 '22 at 16:26

How to call complex Stored Function using Entity Manager?

0 Answers0