There is a more generic problem with function return values in general
and the JPA API as defined not giving the provider any indication that
any of the registered parameters are supposed to map to the function
return. I just assumed this is "outside the scope" of
StoredProcedureQuery; that StoredProcedureQuery explicitly maps to
PROCEDURES (or FUNCTIONS that return REF_CURSOR only).
If that is an inaccurate assumption, then in the more general sense,
what is the indication to the provider that a function is being called?
On Mon 26 Aug 2013 12:33:38 PM CDT, Steve Ebersole wrote:
> I realize providers could use various specific JDBC driver methods of
> calling the actual procedure/function indicated by a
> StoredProcedureQuery.
>
> But given standard JDBC calls, the recommended approach is that
> procedures are called using:
>
> CallableStatement cstmt = connection.prepareCall( "{CALL
> theProcedure(...)}" );
>
> and functions are called using:
>
> CallableStatement cstmt = connection.prepareCall( "{? = CALL
> theFunction(...)}" );
>
>
> But this gets to an important practical implication of supporting
> REF_CURSOR parameters as defined by the JPA 2.1 spec. If you take a
> look at supporting this for 2 popular databases that support
> "returning cursors" in Oracle and PostgreSQL:
>
> For PostgreSQL returning a cursor means:
> 1) The "procedure" will have to be a function.
> 2) The function return will be of type refcursor
> 3) There can be only one such REF_CURSOR parameter (in the JPA
> StoredProcedureQuery object) and it needs to be "first"
> 4) When creating the CallableStatement, we need to use the {? = call
> theFunction(...)} form
>
> For Oracle, returning cursors is supported both via function return
> value and via parameters, meaning:
> 1) We could have a function or a procedure.
> 2) There might be multiple REF_CURSOR parameters and they could occur
> in any order/postion. If the call is to a function, the function may
> or may not have a return type of cursorType (REF_CURSOR).
> 3) When creating the CallableStatement, we actually have no clue
> whether we need the {call procedureName(...)} form or the {?=call
> functionName(...)} form
>
> In a generic sense, or in the Oracle case specifically, what is the
> indication via the JPA API to tell the provider to use the procedure
> or function call syntax?
>
>