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?
