Oracle 12中的本地集合类型是否存在问题？

运行时：

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

文档Database New Features Guide说：

PL/SQL-Specific Data Types Allowed Across the PL/SQL-to-SQL Interface

The table operator can now be used in a PL/SQL program on a collection whose data type is declared in PL/SQL. This also allows the data type to be a PL/SQL associative array. (In prior releases,the collection’s data type had to be declared at the schema level.)

但是,我不知道你的代码为什么不工作,也许这个新功能还是有一个bug.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!