Issue blocking the use of callable statements to invoke Oracle stored procedures/functions through Native SQL Queries

[Jody Grassel <fy...@gmail.com>]

The OpenJPA documentation, section 11.1 (Creating SQL Queries) makes the
following statement:

"In addition to SELECT statements, OpenJPA supports stored procedure
invocations such as SQL queries.  OpenJPA will assume any SQL that does not
begin with the SELECT keyword (ignoring case) is a stored procedure, and
invoke it as such at the JDBC level."

With Oracle, there are a couple of ways to invoke stored procedures:

1) Executing a select query, with the function/stored procedure as a select
input, ie: select selectAllCustomers from dual
2) Callable statement with an output parameter, ie: "{ ? = call
selectAllCustomers() }" or  "begin ? = call selectAllCustomers(); end;"

In all my examples, I generated the query as a native query.

Method #1 seems to work: given a stored function that returns a primitive
value (a number), the numeric variable is either returned directly, or
encapsulated in a List, by calling Query.getResultSingle  or
Query.getResultList() respectively.  Other return types, such as Cursors,
are also returned as an object straight up or in a list.

Method #2 does not work.  The first problem, when I tried using the query "{
? = call selectAllCustomers() }", immediately failed with the following
Exception:

<openjpa-1.2.1-SNAPSHOT-r422266:712930 nonfatal user error>
org.apache.openjpa.persistence.InvalidStateException: Cannot perform a
select on update or delete query: "{ ? = call selectAllCustomers() }".
    at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:244)
    at
org.apache.openjpa.persistence.QueryImpl.getSingleResult(QueryImpl.java:316)
    at ostoredproc.tests.StoredProcTest.test002(StoredProcTest.java:251)

The above Exception was traced to this method in
org.apache.openjpa.persistence.QueryImpl :
private Object execute() {
     if (_query.getOperation() != QueryOperations.OP_SELECT)
          throw new InvalidStateException(_loc.get("not-select-query",
               _query.getQueryString()), null, null, false);

Basically, when getResultSingle() or getResultList() is called, it checks if
the query submitted is a select query.  If it is not, then it throws the
InvalidStateException listed above.  This seems contrary to what the note in
Section 11.1 stated -- instead of executing the query as a stored procedure
call, it immediately rejected it with an InvalidStateException.

So I modified the check to throw the IllegalStateException if the query is
not a native query.  Unfortunately, the query still failed, with the
following new Exception:

<snip>
Caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 1
 at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
 at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
 at
oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1719)
 at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3334)
 at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384)
 at
org.apache.openjpa.lib.jdbc.DelegatingCallableStatement.executeQuery(DelegatingCallableStatement.java:269)
 at
org.apache.openjpa.lib.jdbc.DelegatingCallableStatement.executeQuery(DelegatingCallableStatement.java:257)
 at
org.apache.openjpa.jdbc.kernel.SQLStoreQuery$SQLExecutor.executeQuery(SQLStoreQuery.java:392)
 at
org.apache.openjpa.jdbc.kernel.SQLStoreQuery$SQLExecutor.executeQuery(SQLStoreQuery.java:295)
 ... 26 more

Researching this Exception, it seems that the most likely reason is the
failure to designate the parameter as an output parameter.  Unfortunately,
there is no way to declare a parameter as an output parameter.  The JPA and
OpenJPA Query interfaces only provide means to configure input parameters.
So this is an issue that is blocking this appraoch of invoking stored
procedures/functions on Oracle.

Therefore, my question to the community is to:

1) Verify the statement in Section 11.1 declaring that non-select queries
are executed as procedure calls

2) Determine if procedure calls that do not rely on output parameters are
the only valid procedure/function calls that will be supported by OpenJPA?
If they should be supported, it seems a change to the OpenJPA Query
interface is needed.