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?