Opening refcursor for plsql defined table which is filled with BULK COLLECT-CodePudding

I would like to open cursor for select from plsql table, however I'm getting the error

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

What would be the least verbose solution to convince oracle, to open the cursor?

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

Error

    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

CodePudding user response：

For demonstration purposes, I'm using Scott's DEPT table (as it has only 3 columns; EMP has more of them and I'm kind of lazy creating type with that many columns).

Table contents:

SQL> set serveroutput on
SQL> select * From dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Creating types at SQL level (i.e. not within the PL/SQL procedure):

SQL> create or replace type t_row is object
  2    (deptno   number,
  3     dname    varchar2(20),
  4     loc      varchar2(20));
  5  /

Type created.

SQL> create or replace type t_tab is table of t_row;
  2  /

Type created.

PL/SQL procedure; along with fetching part, I included display of ref cursor contents:

SQL> declare
  2    cursor c is select t_row(deptno, dname, loc) from dept;
  3    pt1   t_tab;
  4    rcur  sys_refcursor;
  5    --
  6    -- local variables to check what's in ref cursor
  7    l_deptno dept.deptno%type;
  8    l_dname  dept.dname%type;
  9    l_loc    dept.loc%type;
 10  begin
 11    open c;
 12    fetch c bulk collect into pt1;
 13    close c;
 14
 15    open rcur for
 16      select * from table(pt1);
 17
 18    -- what's in ref cursor?
 19    loop
 20      fetch rcur into l_deptno, l_dname, l_loc;
 21      exit when rcur%notfound;
 22      dbms_output.put_line(l_dname);
 23    end loop;
 24  end;
 25  /
ACCOUNTING
RESEARCH
SALES
OPERATIONS

PL/SQL procedure successfully completed.

SQL>

Seems to be OK.