Search code examples
javajpaplsqloracle11geclipselink

Calling Oracle function with StoredProcedureQuery?

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?

Solution

  • 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.