运行时：

> 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;
/