ORA-29481: Implicit results cannot be returned to

Question

I wrote a PL/SQL procedure like this:

CREATE OR REPLACE PROCEDURE p_5666 (
  id1 NUMBER := NULL, 
  id2 NUMBER := NULL, 
  id3 NUMBER := NULL
) IS
  c1 SYS_REFCURSOR;
  c2 SYS_REFCURSOR;
  c3 SYS_REFCURSOR;
BEGIN
  IF id1 IS NOT NULL THEN
    OPEN c1 FOR SELECT first_name, last_name FROM t_author WHERE id = id1;
    dbms_sql.return_result (c1);
  END IF;

  IF id2 IS NOT NULL THEN
    OPEN c2 FOR SELECT title FROM t_book WHERE id = id2;
    dbms_sql.return_result (c2);
  END IF;

  IF id3 IS NOT NULL THEN
    OPEN c3 FOR SELECT id3 AS id FROM dual;
    dbms_sql.return_result (c3);
  END IF;
END;

And now, I want to call the above procedure from Java using JDBC as follows (algorithm inspired by this article and this one):

try (Connection cn = new oracle.jdbc.OracleDriver().connect(url, properties);
     Statement s = cn.createStatement()) {
    boolean result = s.execute("begin p_5666(1, 2, 3); end;");

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

        // Note, this call seems to be required in Oracle even for the first result set.
        // In other JDBC drivers (e.g. MySQL, SQL Server), this would skip the first result
        result = s.getMoreResults();

        if (result)
            try (ResultSet rs = s.getResultSet()) {
                System.out.println("\nResult " + i + ":");
                ResultSetMetaData meta = rs.getMetaData();

                while (rs.next())
                    IntStream.rangeClosed(1, meta.getColumnCount())
                    // Using jOOλ's wrapper for lambdas throwing checked exceptions
                             .mapToObj(Unchecked.intFunction(
                                  j -> meta.getColumnName(j) + ": " + rs.getObject(j)))
                             .forEach(System.out::println);
            }
        else if ((s.getUpdateCount()) == -1)
            break fetchLoop;
    }
}

Unfortunately, the above throws the following exception:

java.sql.SQLException: ORA-29481: Implicit results cannot be returned to client.
ORA-06512: at "SYS.DBMS_SQL", line 2785
ORA-06512: at "SYS.DBMS_SQL", line 2779
ORA-06512: at "TEST.P_5666", line 9
ORA-06512: at line 1

What's the reason for this?

Answer 1

I was still using an 11g version of the ojdbc driver. Upgrading to a 12c version of ojdbc fixed the problem and yields the expected result:

Result 1:
FIRST_NAME: George
LAST_NAME: Orwell

Result 2:
TITLE: Animal Farm

Result 3:
ID: 3

This is also explained in this article.