How to get value from nested table in pl sql table

Question

I have created nested table as follow:

CREATE OR REPLACE TYPE  EMP_NO_NAME 
AS OBJECT 
( 
EMPNO NUMBER(4),
ENAME VARCHAR2(20),
JOB VARCHAR2(20),
MGR NUMBER(5),
HIREDATE DATE,
SAL NUMBER(7,2)
);
CREATE OR REPLACE TYPE EMP_TABLE IS TABLE OF EMP_NO_NAME;

-----------------------
CREATE TABLE NESTED_EMP 
(
DEPTNO NUMBER(2) ,
EMPLOYEE EMP_TABLE
)
NESTED TABLE EMPLOYEE STORE AS NESTED_EMPLOYEE;

INSERT INTO NESTED_EMP (DEPTNO,EMPLOYEE)
VALUES (10,EMP_TABLE(EMP_NO_NAME(7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000),
                     EMP_NO_NAME(7782,'CLARK','MANAGER',7839,'09-JUN-81',2450),
                     EMP_NO_NAME(7934,'MILLER','CLERK',7782,'23-JAN-82',1300)
                     )
        );   

INSERT INTO NESTED_EMP (DEPTNO,EMPLOYEE)
VALUES (20,EMP_TABLE(EMP_NO_NAME(7566,'JONES','MANAGER',7839,'02-APR-81',2975),
                      EMP_NO_NAME(7902,'FORD','ANALYST',7566,'03-DEC-81',3000),
                      EMP_NO_NAME(7369,'SMITH','CLERK',7902,'17-DEC-80',800),
                      EMP_NO_NAME(7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000),
                      EMP_NO_NAME(7876,'ADAMS','CLERK',7788,'12-JAN-83',1100)
                      )
       ); 
INSERT INTO NESTED_EMP (DEPTNO,EMPLOYEE)
VALUES (20,EMP_TABLE(EMP_NO_NAME(7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850),
                     EMP_NO_NAME(7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250),
                     EMP_NO_NAME(7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600),
                     EMP_NO_NAME(7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500),
                     EMP_NO_NAME(7900,'JAMES','CLERK',7698,'03-DEC-81',950),
                     EMP_NO_NAME(7521,'WARD','SALESMAN',7698,'22-FEB-81',1250)
                     )
      );

Now I getting the value of nested table in plsql:

    DECLARE 
    CURSOR EMPLOYEE IS 
    select p.* from NESTED_EMP p1 ,table(p1.employee) p;
    V_EMP EMP_TABLE;
    BEGIN 
    FOR  V_EMP IN EMPLOYEE
    LOOP
    EXIT WHEN EMPLOYEE%NOTFOUND;
    END LOOP;
    FOR MYINDEX IN V_EMP.FIRST..V_EMP.LAST
    LOOP 
    DBMS_OUTPUT.PUT_LINE(V_EMP(MYINDEX).ENAME);
    END LOOP;
    END;
    / 
END;

Error report:

ORA-06531: Reference to uninitialized collection ORA-06512: at line 10 06531. 00000 - "Reference to uninitialized collection"

*Cause: An element or member function of a nested table or varray was referenced (where an initialized collection is needed) without the collection having been initialized.

*Action: Initialize the collection with an appropriate constructor or whole-object assignment.

How to get nested table value in plsql table ?

Answer 1

The problem with your code is that V_EMP is not actually of type EMP_TABLE. Rather, it's an EMPLOYEE.ROWTYPE. When you initialize a cursor for loop, the variable is automatically made an appropriate ROWTYPE, overriding any previous declarations.

The good news is that, since you've already referenced the nested table in the query, you don't need to do so in the loop (it's already been exploded). Your PL/SQL can be vastly simplified:

DECLARE
   CURSOR employee IS
      SELECT p.*
      FROM   nested_emp p1 CROSS JOIN TABLE (p1.employee) p;
BEGIN
   FOR v_emp IN employee LOOP
      DBMS_OUTPUT.put_line (v_emp.ename);
   END LOOP;
END;
/

You'll notice the EXIT WHEN was removed as well. A cursor for loop terminates automatically after the last record.

---

An alternative would be to not explode the nested table in the query. Then you would need two loops:

DECLARE
   CURSOR employee IS
      SELECT p.*
      FROM   nested_emp p;
BEGIN
   FOR v_emp IN employee LOOP
      for i in v_emp.employee.first..v_emp.employee.last loop
        DBMS_OUTPUT.put_line (v_emp.employee(i).ename);
      end loop;
   END LOOP;
END;
/