What's the correct way to fetch all (possibly implicit) results from an Oracle query with JDBC?

Question

Since Oracle 12c, we can fetch implicit cursors from clients. For instance, it is possible to run the following PL/SQL anonymous block in SQL Developer

DECLARE
  c1 sys_refcursor;
  c2 sys_refcursor;
BEGIN
  OPEN c1 FOR SELECT 1 AS a FROM dual;
  dbms_sql.return_result(c1);
  OPEN c2 FOR SELECT 2 AS b FROM dual;
  dbms_sql.return_result(c2);
END;

To get the following result:

ResultSet #1
A                                       
--------------------------------------- 
1                                       

ResultSet #2
B                                       
--------------------------------------- 
2

This works almost like a MySQL or SQL Server batch (e.g. as shown in this article), so I'm thinking that we should be able to run the following code:

try (Statement s = connection.createStatement()) {
    boolean result = s.execute(sql); // Plug above SQL here

    fetchLoop:
    for (int i = 0;; i++) {
        if (i > 0) result = s.getMoreResults();
        System.out.println(result);

        if (result)
            try (ResultSet rs = s.getResultSet()) {
                System.out.println("Fetching result " + i);
                // ...
            }
        else if (s.getUpdateCount() == -1)
            break fetchLoop;
    }
}

Which results in an error with the ojdbc6 version 12.1.0.1.0:

true
java.sql.SQLException: No resultset available
at oracle.jdbc.driver.OracleStatement.getResultSet(OracleStatement.java:3369)
at oracle.jdbc.driver.OracleStatementWrapper.getResultSet(OracleStatementWrapper.java:388)
at Oracle.main(Oracle.java:46)

This seems to be in violation of the Statement.execute() method, whose Javadoc indicates:

Returns:
true if the first result is a ResultSet object; false if it is an update count or there are no results

So, once Statement.execute() yields true, Statement.getResultSet() should return a result set, in my opinion. A workaround would be:

try (Statement s = connection.createStatement()) {
    s.execute(sql);                          // WORKAROUND: Ignore this result

    fetchLoop:
    for (int i = 0;; i++) {
        boolean result = s.getMoreResults(); // WORKAROUND: Take the result from here
        System.out.println(result);

        if (result)
            try (ResultSet rs = s.getResultSet()) {
                System.out.println("Fetching result " + i);
                // ...
            }
        else if (s.getUpdateCount() == -1)
            break fetchLoop;
    }
}

The result is now:

true
Fetching result 0
true
Fetching result 1
false

But that appears to be wrong API usage. According to my understanding of the JDBC spec, this "improved" loop would now skip the first result set.

To make matters worse, prepared statements behave differently. The following code:

try (PreparedStatement s = cn.prepareStatement(sql)) {
    boolean result = s.execute();

    fetchLoop:
    for (int i = 0;; i++) {
        if (i > 0) result = s.getMoreResults();
        System.out.println(result);

        if (result)
            try (ResultSet rs = s.getResultSet()) {
                System.out.println("Fetching result " + i);
                // ...
            }
        else if (s.getUpdateCount() == -1)
            break fetchLoop;
    }
}

Doesn't fetch any result sets but simply quits:

false

It again works this way:

try (PreparedStatement s = cn.prepareStatement(sql)) {
    s.execute();

    fetchLoop:
    for (int i = 0;; i++) {
        boolean result = s.getMoreResults();
        System.out.println(result);

        if (result)
            try (ResultSet rs = s.getResultSet()) {
                System.out.println("Fetching result " + i);
                // ...
            }
        else if (s.getUpdateCount() == -1)
            break fetchLoop;
    }
}

true
Fetching result 0
true
Fetching result 1
false

My questions (finally)

Assume that I'm writing generic JDBC client code, that doesn't know what the SQL string contains (it might just be an ordinary query). I want to fetch all the result sets that I may possibly get.

• Does ojdbc violate the JDBC specs here?
• What's the correct way to fetch all result sets from ojdbc, if the SQL string is unknown?

To be clear, the above workarounds are wrong for ordinary queries like SELECT 1 FROM dual.

Answer 1

For the time being (while this may or may not be a bug in ojdbc), I've found this nasty workaround to cover all usages of the JDBC API (without knowing what the SQL string produces):

/* Alternatively, use this for non-PreparedStatements:
try (Statement s = cn.createStatement()) {
    Boolean result = s.execute(sql); */
try (PreparedStatement s = cn.prepareStatement(sql)) {
    // Use good old three-valued boolean logic
    Boolean result = s.execute();

    fetchLoop:
    for (int i = 0;; i++) {

        // Check for more results if not already done in this iteration
        if (i > 0 && result == null)
            result = s.getMoreResults();
        System.out.println(result);

        if (result) {
            result = null;

            try (ResultSet rs = s.getResultSet()) {
                System.out.println("Fetching result " + i);
            }
            catch (SQLException e) {
                // Ignore ORA-17283: No resultset available
                if (e.getErrorCode() == 17283)
                    continue fetchLoop;
                else
                    throw e;
            }
        }
        else if (s.getUpdateCount() == -1)
            // Ignore -1 value if there is one more result!
            if (result = s.getMoreResults())
                continue fetchLoop;
            else
                break fetchLoop;
    }
}