1

I'm trying to call an Oracle PL/SQL function using javax.persistence.StoredProcedureQuery and I get the error PLS-00221: 'function_name' is not a procedure or is undefined

So I assume its not possible to call an Oracle function using StoredProcedureQuery?

I don't see JPA offering an interface to call functions(as opposed to stored procedures). So how do I accomplish it?

Roland
  • 7,525
  • 13
  • 61
  • 124

2 Answers2

2

Sample Oracle Function:

CREATE OR REPLACE FUNCTION f_get_random_number
  RETURN NUMBER
AS
BEGIN
  RETURN TRUNC(dbms_random.value(1,100));
END;

Solution 1: using SQL Query

BigDecimal val = (BigDecimal)entityManager
                    .createNativeQuery("SELECT f_get_random_number FROM DUAL")
                    .getSingleResult();

Solution 2: using JDBC API

Session session = entityManager.unwrap( Session.class );         
    Integer out = session.doReturningWork( 
        connection -> {
        try (CallableStatement function = connection.prepareCall("{ ? = call f_get_random_number }" )) {
            function.registerOutParameter( 1, Types.INTEGER );
            function.execute();
            return function.getInt(1);
        } 
    } );
ukchaudhary
  • 377
  • 3
  • 7
  • Yes you can execute an SQL query that will call the function. But I'm looking for a way to call the function directly, as you can do with stored procedures. – Roland Nov 06 '17 at 07:21
  • It can be achieved through JDBC API as shown in solution 2. – ukchaudhary Nov 06 '17 at 07:43
2

As an addition to ukchaudhary's answer:

So I assume its not possible to call an Oracle function using StoredProcedureQuery?

Yes, exactly. Oracle has both stored procedures and functions. StoredProcedureQuery can be used to call stored procedures, but it cannot call stored functions, at least not on Oracle (hence the name).

To call a function, JPA offers the FUNCTION keyword in JPA. This allows invoking a function from inside a JPQL query. For example, a function "hasGoodCredit" is called like this:

SELECT c FROM Customer c
WHERE FUNCTION(‘hasGoodCredit’, c.balance, c.creditLimit)

See JPA specification, Invocation of Predefined and User-defined Database Functions.

However, FUNCTION only works inside a regular JPQL statement (SELECT, UPDATE, DELETE etc.), so it cannot be used for Oracle functions which perform data manipulation (DML), as these can only be invoked separately.

Therefore, such functions cannot be invoked via JPA. You will have to use JDBC or a native query.

sleske
  • 81,358
  • 34
  • 189
  • 227
  • @Roland: Hey - a question almost ten years old, and my answer is accepted within hours. Thanks a lot! – sleske May 16 '23 at 10:43