[hibernate-dev] Function support in ProcedureCall/StoredProcedureQuery

RE: https://hibernate.atlassian.net/browse/HHH-8445 I'd like to get opinions on this. Essentially the JPA spec (StoredProcedureQuery) is silent on how/if calling database functions through this API is supposed to be supported. I asked for clarification of this from the rest of the EG, but got no responses. So at this point we are on our own to implement this as we see fit. And the problem is that we will need to support it. Some databases only support REF_CURSOR "parameters" via function return. In fact, as far as I know, only Oracle supports returning cursors from procedures via parameters (although on HHH-8445 Christian claims DB2 does as well); for all other databases which support returning cursors at all, doing so requires a function where the cursor is the function return. There are 2 aspects to the distinction between a procedure call and a function call. First is the syntax used. Second is handling of "parameter registrations". A procedure is called using the syntax "{call theProcedureName(...)}"; a function using "{?=call theFunctionName(...)}". Given a call that has just one "parameter registration" representing a cursor, the difference would be "{call the_proc(?)}" versus "{?=call the_func()}". JPA disallows mixing of named and positional parameter registrations through StoredProcedureQuery. That is potentially a problem here. The function return is inherently positional (as far as I know the JDBC spec does not discuss metadata about a function return, let alone naming of it). If the StoredProcedureQuery is defined using positional parameters, no problem. If the StoredProcedure is defined using named parameters, that is where we run into this potential problem mainly because the spec says that the order in which parameters are registered is irrelevant. In my opinion, ideally the JPA spec would have defined a separate method to register a "function return". Luckily since the spec does not say anything and the EG never responded we are free to do WhateverMakesSense :) I have an example of what "providing a separate method to register the function return" might look like in the comments of HHH-8445. Ideally we'd allow access to the function return via the same mechanism as registered "output" (IN/INOUT/REF_CURSOR) parameters. Just not sure that is possible. The tricky part is that (via JPA StoredProcedureQuery at least) access to parameter output values are only defined to be available by name and position. The other downside to using a separate method, as of now at least, would be the difficulty to hook in to JPA @NamedStoredProcedureQuery support. We'd either need to define a new @NamedFunctionCallQuery with explicit support for defining the function return, or go a different route altogether (using query hints maybe).