ORA-29481：当从JDBC调用Oracle 12c过程时，无法将隐式结果返回到客户端 [英] ORA-29481: Implicit results cannot be returned to client when calling Oracle 12c procedure from JDBC

问题描述

我写了一个这样的PL / SQL程序：

  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 
如果id1不是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; 
 
如果id3不是NULL，那么
 OPEN c3 FOR SELECT id3 AS id FROM dual; 
 dbms_sql.return_result（c3）; 
 END IF; 
 END; 
  

现在，我想从Java使用JDBC调用上述过程，如下所示（由本文启发的算法< a>和此一）：

  try（Connection cn = new oracle.jdbc.OracleDriver（）。connect（url，properties）; 
语句s = cn.createStatement（））{
 boolean result = s.execute（begin p_5666（1，2，3）; end;）; 
 
 fetchLoop：
 for（int i = 1 ;; i ++）{
 
 //注意，这种调用似乎在Oracle中是必需的，甚至对于第一个结果组。 
 //在其他JDBC驱动程序（例如MySQL，SQL Server）中，这将跳过第一个结果
 result = s.getMoreResults（）; 
 
 if（result）
 try（ResultSet rs = s.getResultSet（））{
 System.out.println（\\\
Result+ i +：） ; 
 ResultSetMetaData meta = rs.getMetaData（）; 
 
 while（rs.next（））
 IntStream.rangeClosed（1，meta.getColumnCount（））
 //使用jOOλ的包装器lambdas抛出异常
 .mapToObj（Unchecked.intFunction（
j  - > meta.getColumnName（j）+：+ rs.getObject（j）））
 .forEach（System.out :: println）; 
} 
 else if（（s.getUpdateCount（））== -1）
 break fetchLoop;不幸的是，上面的代码引发了以下异常： 
} 
} 
  
  java.sql.SQLException：ORA-29481：隐式结果无法返回到客户端。 
 ORA-06512：在SYS.DBMS_SQL，行2785 
 ORA-06512：在SYS.DBMS_SQL，行2779 
 ORA-06512：在TEST.P_5666 9 
 ORA-06512：在第1行
  
原因是什么？
解决方案
我还在使用11g版本的ojdbc驱动程序。升级到12c版本的ojdbc解决了问题，并产生了预期的结果：
 
 
结果1：
 
 FIRST_NAME ：George▲
 LAST_NAME：Orwell MALE 
 
 
 
结果2：
 
 TITLE：Animal Farm 
 
 
 
结果3：
 
 ID：3 
 
 
 a href =http://apprize.info/php/oracle_3/2.html =nofollow noreferrer>这也在本文中进行了说明。
 
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?
解决方案
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.

                        
这篇关于ORA-29481：当从JDBC调用Oracle 12c过程时，无法将隐式结果返回到客户端的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！