sql - Oracle 12 是否有 SQL 中本地集合类型的问题？

Question

长话短说，我建议讨论您在下面看到的代码。

运行时:

Oracle 11 编译器引发

"PLS-00306: wrong number or types of arguments tips in call to 'PIPE_TABLE'"

"PLS-00642: Local Collection Types Not Allowed in SQL Statement"

Oracle 12 编译以下包没有这样的警告，但是我们在运行时有一个惊喜

when executing the anonymous block as is - everything is fine (we may pipe some rows in the pipe_table function - it doesn't affect)

now let's uncomment the line with hello; or put there a call to any procedure, and run the changed anonumous block again we get "ORA-22163: left hand and right hand side collections are not of same type"

问题是:
Oracle 12 是否允许 SQL 中的本地集合类型？
如果是，那么 PACKAGE buggy_report 的代码有什么问题？ ?

CREATE OR REPLACE PACKAGE buggy_report IS

  SUBTYPE t_id IS NUMBER(10);
  TYPE t_id_table IS TABLE OF t_id;

  TYPE t_info_rec IS RECORD ( first NUMBER );
  TYPE t_info_table IS TABLE OF t_info_rec;
  TYPE t_info_cur IS REF CURSOR RETURN t_info_rec;

  FUNCTION pipe_table(p t_id_table) RETURN t_info_table PIPELINED;

  FUNCTION get_cursor RETURN t_info_cur;

END buggy_report;
/

CREATE OR REPLACE PACKAGE BODY buggy_report IS

  FUNCTION pipe_table(p t_id_table) RETURN t_info_table PIPELINED IS
    l_table t_id_table;
    BEGIN
      l_table := p;
    END;

  FUNCTION get_cursor RETURN t_info_cur IS
    l_table  t_id_table;
    l_result t_info_cur;
    BEGIN

      OPEN l_result FOR SELECT * FROM TABLE (buggy_report.pipe_table(l_table));

      RETURN l_result;
    END;
END;
/

DECLARE
  l_cur buggy_report.t_info_cur;
  l_rec l_cur%ROWTYPE;
  PROCEDURE hello IS BEGIN NULL; END;
BEGIN

  l_cur := buggy_report.get_cursor();

  -- hello;

  LOOP
    FETCH l_cur INTO l_rec;
    EXIT WHEN l_cur%NOTFOUND;
  END LOOP;

  CLOSE l_cur;

  dbms_output.put_line('success');
END;
/

Answer 1

在进一步的实验中，我们发现问题比想象的更深。

例如，包 buggy_report 中使用的不同元素我们可以得到一个 ORA-03113: end-of-file on communication channel运行脚本时(在问题中)。可以通过更改 t_id_table 的类型来完成至 VARRAY或 TABLE .. INDEX BY .. .有很多方法和变化导致我们出现不同的异常(exception)情况，这与本文无关。

比较有趣的一点是buggy_report的编译时间。包装规范最多可能需要 25 秒，
正常情况下大约需要 0.05 秒。我可以肯定地说这取决于 TYPE t_id_table 的存在pipe_table 中的参数函数声明和“长时间编译”发生在 40% 的安装案例中。所以似乎是local collection types in SQL的问题在编译过程中潜在地出现。

所以我们看到Oracle 12.1.0.2在SQL中使用本地集合类型的实现显然有一个bug。

获得的最小示例 ORA-22163和 ORA-03113正在关注。我们假设相同 buggy_report包中的问题。

-- produces 'ORA-03113: end-of-file on communication channel'
DECLARE   
  l_cur buggy_report.t_info_cur;

  FUNCTION get_it RETURN buggy_report.t_info_cur IS BEGIN RETURN buggy_report.get_cursor(); END;    
BEGIN
   l_cur := get_it();

   dbms_output.put_line('');
END;
/

-- produces 'ORA-22163: left hand and right hand side collections are not of same type'
DECLARE  
  l_cur buggy_report.t_info_cur;

  PROCEDURE hello IS BEGIN NULL; END;
BEGIN
  l_cur := buggy_report.get_cursor;

  -- comment `hello` and exception disappears
  hello;

  CLOSE l_cur;
END;
/