如何解决PL/SQL中为索引表打开REF CURSOR时的ORA-22905错误？

要对PL/SQL表执行SELECT语句打开游标时，触发了如下错误：

ORA-22905: cannot access rows from a non-nested table item

原始代码 #

DECLARE
   CURSOR c
   IS
      SELECT *
      from emp
      offset 0 rows fetch first 10 rows only;

   TYPE table_type IS TABLE OF emp%ROWTYPE
         INDEX BY BINARY_INTEGER
   ;

   pt1   table_type;
   rcur  sys_refcursor;

BEGIN

   OPEN c;
   FETCH c BULK COLLECT INTO pt1;
   CLOSE c;

  open rcur for
    select * from table(pt1);
END;
/

报错详情 #

select * from table(pt1);
                        *
ERROR at line 23:
ORA-06550: line 23, column 25:
PLS-00382: expression is of wrong type
ORA-06550: line 23, column 19:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 23, column 5:
PL/SQL: SQL Statement ignored

原因分析 #

你定义的table_type是带INDEX BY BINARY_INTEGER的关联数组（索引表），属于PL/SQL专属类型，无法被SQL引擎识别，因此不能在SQL语句的TABLE()函数中使用。

最简洁的解决方法 #

将PL/SQL表类型修改为无索引的嵌套表类型（去掉INDEX BY BINARY_INTEGER子句），使类型兼容SQL层，即可在TABLE()中调用。

修改后的代码：

DECLARE
   CURSOR c
   IS
      SELECT *
      from emp
      offset 0 rows fetch first 10 rows only;

   -- 移除INDEX BY BINARY_INTEGER，改为嵌套表类型
   TYPE table_type IS TABLE OF emp%ROWTYPE;

   pt1   table_type;
   rcur  sys_refcursor;

BEGIN
   OPEN c;
   FETCH c BULK COLLECT INTO pt1;
   CLOSE c;

   open rcur for
      select * from table(pt1);
END;
/

补充说明 #

如果需要保留关联数组的特性，也可以通过将关联数组转换为嵌套表的方式实现，但直接修改类型是最简洁的方案。

内容的提问来源于stack exchange，提问作者mpapec