Invoking a PL/SQL function from EclipseLink

Question

I'm trying to execute a PL/SQL function in EclipseLink which uses Oracle Specific types(eg. Boolean).

I've tried using PLSQLStoredProcedureCall which complains that I'm not executing a procedure and I have tried using StoredFunctionCall but that returns

PLS-00382: expression is of wrong type

Has anyone developed a solution to invoke functions in EclipseLink with Oracle Types? I have heard that it would be possible to extend StoredFunctionCall but I'd rather leverage existing functionality as much as possible.

Answer 1

PL/SQL functions can receive or return a BOOLEAN. However these cannot be called from SQL because Oracle SQL doesn't understand the concept of boolean. Since the primary difference between a function and a procedure is that the former can be called from SQL, I've never seen the point of a creating a function that receives or returns a boolean.

So I'd replace it with a procedure with an out parameter, or at least have a wrapper procedure that calls the function..

CREATE OR REPLACE FUNCTION f_x (p_id IN NUMBER) RETURN BOOLEAN IS
BEGIN
   RETURN TRUE;
END f_x;

CREATE OR REPLACE PROCEDURE p_x (i_id IN NUMBER, o_val OUT BOOLEAN) IS
BEGIN
  o_val := f_x(i_id);
END p_x;