-- USER-DEFINED OBJECT TYPE AND NESTED TABLE - WORKS GOOD

-- USER-DEFINED OBJECT TYPE
CREATE OR REPLACE TYPE EMP_OT AS OBJECT
(
  EMPNO    NUMBER(12),
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4),
  HIREDATE DATE,
  SAL      NUMBER(7,2),
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER(2)
);
/
-- USER DEFINED NESTED TABLE 
CREATE OR REPLACE TYPE EMP_AAT IS TABLE OF EMP_OT;
/
-- PACKAGE DEFINED REF CUR TYPE
CREATE OR REPLACE PACKAGE TYPE_DEF_PKG 
AS
  TYPE EMP_RCT IS REF CURSOR RETURN EMP%ROWTYPE;
END;
/
-- TABLE FUNCTION
CREATE OR REPLACE FUNCTION TBL_FUNC (CV_IN IN TYPE_DEF_PKG.EMP_RCT)
RETURN EMP_AAT
IS
  L_ROW_AS_OBJECT EMP_OT := EMP_OT(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
  L_ROW_FROM_QUERY CV_IN%ROWTYPE;
  RETVAL EMP_AAT := EMP_AAT();
BEGIN
  LOOP
    FETCH CV_IN INTO L_ROW_FROM_QUERY;
    EXIT WHEN CV_IN%NOTFOUND;
    
    L_ROW_AS_OBJECT.EMPNO := L_ROW_FROM_QUERY.EMPNO;
    L_ROW_AS_OBJECT.ENAME := L_ROW_FROM_QUERY.ENAME;
    L_ROW_AS_OBJECT.JOB := L_ROW_FROM_QUERY.JOB;
    L_ROW_AS_OBJECT.MGR := L_ROW_FROM_QUERY.MGR;
    L_ROW_AS_OBJECT.HIREDATE := L_ROW_FROM_QUERY.HIREDATE;
    L_ROW_AS_OBJECT.SAL := L_ROW_FROM_QUERY.SAL;
    L_ROW_AS_OBJECT.COMM := L_ROW_FROM_QUERY.COMM;
    L_ROW_AS_OBJECT.DEPTNO := L_ROW_FROM_QUERY.DEPTNO;
    
    RETVAL.EXTEND;
    RETVAL(RETVAL.LAST) := L_ROW_AS_OBJECT;
  END LOOP;
  
  CLOSE CV_IN;
  
  RETURN RETVAL;
END;
/
-- SELECT USING TABLE OPERATOR
SELECT * 
FROM TABLE(TBL_FUNC(CURSOR(SELECT * FROM EMP)));
/

-- PACKAGE-DEFINED OBJECT TYPE AND NESTED TABLE - DOESNT WORK
CREATE OR REPLACE PACKAGE TYPE_DEF_PKG 
AS
  TYPE EMP_RCT IS REF CURSOR RETURN EMP%ROWTYPE;
  TYPE EMP_RT IS RECORD (
                        EMPNO    NUMBER(12),
                        ENAME    VARCHAR2(10),
                        JOB      VARCHAR2(9),
                        MGR      NUMBER(4),
                        HIREDATE DATE,
                        SAL      NUMBER(7,2),
                        COMM     NUMBER(7,2),
                        DEPTNO   NUMBER(2)
                      );
  TYPE EMP_NTT IS TABLE OF EMP_RT;
END;
/
-- TABLE FUNCTION
CREATE OR REPLACE FUNCTION TBL_FUNC (CV_IN IN TYPE_DEF_PKG.EMP_RCT)
RETURN TYPE_DEF_PKG.EMP_NTT
IS
  L_ROW TYPE_DEF_PKG.EMP_RT; 
  L_ROW_FROM_QUERY CV_IN%ROWTYPE;
  RETVAL TYPE_DEF_PKG.EMP_NTT := TYPE_DEF_PKG.EMP_NTT();
BEGIN
  LOOP
    FETCH CV_IN INTO L_ROW_FROM_QUERY;
    EXIT WHEN CV_IN%NOTFOUND;
    
    L_ROW.EMPNO := L_ROW_FROM_QUERY.EMPNO;
    L_ROW.ENAME := L_ROW_FROM_QUERY.ENAME;
    L_ROW.JOB := L_ROW_FROM_QUERY.JOB;
    L_ROW.MGR := L_ROW_FROM_QUERY.MGR;
    L_ROW.HIREDATE := L_ROW_FROM_QUERY.HIREDATE;
    L_ROW.SAL := L_ROW_FROM_QUERY.SAL;
    L_ROW.COMM := L_ROW_FROM_QUERY.COMM;
    L_ROW.DEPTNO := L_ROW_FROM_QUERY.DEPTNO;
    
    RETVAL.EXTEND;
    RETVAL(RETVAL.LAST) := L_ROW;
  END LOOP;
  
  CLOSE CV_IN;
  
  RETURN RETVAL;
END;
/
-- SELECT USING TABLE OPERATOR
SQL> SELECT *
  2  FROM TABLE(TBL_FUNC(CURSOR(SELECT * FROM EMP)));
FROM TABLE(TBL_FUNC(CURSOR(SELECT * FROM EMP)))
           *
ERROR AT LINE 2:
ORA-00902: INVALID DATATYPE

SQL> CREATE OR REPLACE PACKAGE TYPE_DEF_PKG
  2  AS
  3    TYPE EMP_RCT IS REF CURSOR RETURN EMP_OT;
  4  END;
  5  /

Warning: Package created with compilation errors.

SQL> show err
Errors for PACKAGE TYPE_DEF_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3      PL/SQL: Declaration ignored
3/19     PLS-00362: invalid cursor return type; 'EMP_OT' must be a record
         type

SQL>

SQL> CREATE OR REPLACE PACKAGE TYPE_DEF_PKG
  2  AS
  3    CURSOR EMP_CUR IS SELECT * FROM EMP;
  4    TYPE EMP_RCT IS REF CURSOR RETURN EMP_CUR%ROWTYPE;
  5    TYPE EMP_AAT IS TABLE OF EMP_CUR%ROWTYPE INDEX BY PLS_INTEGER;
  6    FUNCTION TBL_FUNC (CV_IN IN EMP_RCT) RETURN EMP_AAT PIPELINED;
  7  END;
  8  /

Warning: Package created with compilation errors.

SQL> show err
Errors for PACKAGE TYPE_DEF_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/12     PLS-00630: pipelined functions must have a supported collection
         return type

SQL> CREATE OR REPLACE PACKAGE TYPE_DEF_PKG
  2  AS
  3    CURSOR EMP_CUR IS SELECT * FROM EMP;
  4    TYPE EMP_RCT IS REF CURSOR RETURN EMP_CUR%ROWTYPE;
  5    --TYPE EMP_AAT IS TABLE OF EMP_CUR%ROWTYPE INDEX BY PLS_INTEGER;
  6    TYPE EMP_NTT IS TABLE OF EMP_CUR%ROWTYPE;
  7    FUNCTION TBL_FUNC (CV_IN IN EMP_RCT) RETURN EMP_NTT/*EMP_AAT*/;
  8  END;
  9  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY TYPE_DEF_PKG AS
  2    FUNCTION TBL_FUNC (CV_IN IN EMP_RCT) RETURN EMP_NTT/*EMP_AAT*/
  3    IS
  4      RETVAL EMP_NTT/*EMP_AAT*/;
  5    BEGIN
  6      FETCH CV_IN BULK COLLECT INTO RETVAL;
  7      CLOSE CV_IN;
  8      RETURN RETVAL;
  9    END;
 10  END;
 11  /

Package body created.

SQL> SELECT *
  2  FROM TABLE(TYPE_DEF_PKG.TBL_FUNC(CURSOR(SELECT * FROM EMP)));
FROM TABLE(TYPE_DEF_PKG.TBL_FUNC(CURSOR(SELECT * FROM EMP)))
           *
ERROR at line 2:
ORA-00902: invalid datatype

SQL> CREATE OR REPLACE PACKAGE TYPE_DEF_PKG
  2  AS
  3    CURSOR EMP_CUR IS SELECT EMPNO, ENAME FROM EMP;
  4    TYPE EMP_RCT IS REF CURSOR RETURN EMP_CUR%ROWTYPE;
  5    TYPE EMP_AAT IS TABLE OF EMP_CUR%ROWTYPE INDEX BY VARCHAR2(100);
  6    FUNCTION TBL_FUNC_AA (CV_IN IN EMP_RCT) RETURN EMP_AAT;
  7  END;
  8  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY TYPE_DEF_PKG AS
  2    FUNCTION TBL_FUNC_AA (CV_IN IN EMP_RCT) RETURN EMP_AAT
  3     IS
  4       RETVAL EMP_AAT;
  5       REC CV_IN%ROWTYPE;
  6    BEGIN
  7       --FETCH CV_IN BULK COLLECT INTO RETVAL;
  8         LOOP
  9            FETCH CV_IN INTO REC;
 10            EXIT WHEN CV_IN%NOTFOUND;
 11            RETVAL(REC.ENAME).EMPNO := REC.EMPNO;
 12            RETVAL(REC.ENAME).ENAME := REC.ENAME;
 13         END LOOP;
 14       CLOSE CV_IN;
 15       RETURN RETVAL;
 16    END;
 17  END;
 18  /

Package body created.

SQL> DECLARE
  2    L_AA_VAR TYPE_DEF_PKG.EMP_AAT;
  3    CUR_VAR_AA TYPE_DEF_PKG.EMP_RCT;
  4    L_IDX VARCHAR2(100);
  5  BEGIN
  6    OPEN CUR_VAR_AA FOR SELECT EMPNO, ENAME FROM EMP;
  7    L_AA_VAR := TYPE_DEF_PKG.TBL_FUNC_AA(CUR_VAR_AA);
  8
  9    L_IDX := L_AA_VAR.FIRST;
 10    LOOP
 11      EXIT WHEN L_IDX IS NULL;
 12      DBMS_OUTPUT.PUT_LINE(L_AA_VAR(L_IDX).EMPNO || ' - ' || L_AA_VAR(L_IDX).ENAME);
 13      L_IDX := L_AA_VAR.NEXT(L_IDX);
 14    END LOOP;
 15
 16    FOR REC IN (SELECT * FROM TABLE(L_AA_VAR))
 17      LOOP
 18        NULL;
 19        --DBMS_OUTPUT.PUT_LINE(REC.EMPNO || ' - ' || REC.ENAME);
 20      END LOOP;
 21  END;
 22  /
  FOR REC IN (SELECT * FROM TABLE(L_AA_VAR))
                                  *
ERROR at line 16:
ORA-06550: line 16, column 35:
PLS-00382: expression is of wrong type
ORA-06550: line 16, column 29:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 16, column 15:
PL/SQL: SQL Statement ignored

SQL> CREATE OR REPLACE PACKAGE TYPE_DEF_PKG
  2  AS
  3    CURSOR EMP_CUR IS SELECT EMPNO, ENAME FROM EMP;
  4    TYPE EMP_RCT IS REF CURSOR RETURN EMP_CUR%ROWTYPE;
  5    TYPE EMP_AAT IS TABLE OF EMP_CUR%ROWTYPE INDEX BY PLS_INTEGER;
  6    FUNCTION TBL_FUNC_AA (CV_IN IN EMP_RCT) RETURN EMP_AAT;
  7  END;
  8  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY TYPE_DEF_PKG AS
  2    FUNCTION TBL_FUNC_AA (CV_IN IN EMP_RCT) RETURN EMP_AAT
  3     IS
  4       RETVAL EMP_AAT;
  5       REC CV_IN%ROWTYPE;
  6    BEGIN
  7       --FETCH CV_IN BULK COLLECT INTO RETVAL;
  8         LOOP
  9            FETCH CV_IN INTO REC;
 10            EXIT WHEN CV_IN%NOTFOUND;
 11            RETVAL(REC.EMPNO).EMPNO := REC.EMPNO;
 12            RETVAL(REC.EMPNO).ENAME := REC.ENAME;
 13         END LOOP;
 14       CLOSE CV_IN;
 15       RETURN RETVAL;
 16    END;
 17  END;
 18  /

Package body created.

SQL> DECLARE
  2    L_AA_VAR TYPE_DEF_PKG.EMP_AAT;
  3    CUR_VAR_AA TYPE_DEF_PKG.EMP_RCT;
  4    L_IDX PLS_INTEGER;
  5  BEGIN
  6    OPEN CUR_VAR_AA FOR SELECT EMPNO, ENAME FROM EMP;
  7    L_AA_VAR := TYPE_DEF_PKG.TBL_FUNC_AA(CUR_VAR_AA);
  8
  9    L_IDX := L_AA_VAR.FIRST;
 10    LOOP
 11      EXIT WHEN L_IDX IS NULL;
 12      DBMS_OUTPUT.PUT_LINE(L_AA_VAR(L_IDX).EMPNO || ' - ' || L_AA_VAR(L_IDX).ENAME);
 13      L_IDX := L_AA_VAR.NEXT(L_IDX);
 14    END LOOP;
 15
 16    FOR REC IN (SELECT * FROM TABLE(L_AA_VAR))
 17      LOOP
 18        NULL;
 19        --DBMS_OUTPUT.PUT_LINE(REC.EMPNO);
 20      END LOOP;
 21  END;
 22  /

PL/SQL procedure successfully completed.

SQL> DECLARE
  2    CUR_VAR_AA TYPE_DEF_PKG.EMP_RCT;
  3  BEGIN
  4    OPEN CUR_VAR_AA FOR SELECT EMPNO, ENAME FROM EMP;
  5
  6    FOR REC IN (SELECT * FROM TABLE(TYPE_DEF_PKG.TBL_FUNC_AA(CUR_VAR_AA)))
  7      LOOP
  8        NULL;
  9        --DBMS_OUTPUT.PUT_LINE(REC.EMPNO);
 10      END LOOP;
 11  END;
 12  /
  FOR REC IN (SELECT * FROM TABLE(TYPE_DEF_PKG.TBL_FUNC_AA(CUR_VAR_AA)))
                                               *
ERROR at line 6:
ORA-06550: line 6, column 48:
PLS-00382: expression is of wrong type
ORA-06550: line 6, column 29:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 6, column 15:
PL/SQL: SQL Statement ignored

SQL> DECLARE
  2    CUR_VAR_AA TYPE_DEF_PKG.EMP_RCT;
  3    L_AA_VAR TYPE_DEF_PKG.EMP_AAT;
  4  BEGIN
  5    OPEN CUR_VAR_AA FOR SELECT EMPNO, ENAME FROM EMP;
  6    L_AA_VAR := TYPE_DEF_PKG.TBL_FUNC_AA(CUR_VAR_AA);
  7
  8    FOR REC IN (SELECT * FROM TABLE(L_AA_VAR))
  9      LOOP
 10        NULL;
 11        --DBMS_OUTPUT.PUT_LINE(REC.EMPNO);
 12      END LOOP;
 13  END;
 14  /

PL/SQL procedure successfully completed.

FOR REC IN (SELECT * FROM TABLE(L_AA_VAR))
 LOOP
   NULL;
   --DBMS_OUTPUT.PUT_LINE(REC.EMPNO || ' - ' || REC.ENAME);
END LOOP; 

SQL> CREATE OR REPLACE PACKAGE TYPE_DEF_PKG
  2  AS
  3    CURSOR EMP_CUR IS SELECT EMPNO, ENAME FROM EMP;
  4    TYPE EMP_RCT IS REF CURSOR RETURN EMP_CUR%ROWTYPE;
  5    TYPE EMP_AAT IS TABLE OF EMP_CUR%ROWTYPE INDEX BY PLS_INTEGER;
  6    FUNCTION TBL_FUNC_AA (CV_IN IN EMP_RCT) RETURN EMP_AAT;
  7  END;
  8  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY TYPE_DEF_PKG AS
  2    FUNCTION TBL_FUNC_AA (CV_IN IN EMP_RCT) RETURN EMP_AAT
  3    IS
  4      RETVAL EMP_AAT;
  5      REC CV_IN%ROWTYPE;
  6    BEGIN
  7      --FETCH CV_IN BULK COLLECT INTO RETVAL;
  8      LOOP
  9          FETCH CV_IN INTO REC;
 10          EXIT WHEN CV_IN%NOTFOUND;
 11          RETVAL(REC.EMPNO).EMPNO := REC.EMPNO;
 12          RETVAL(REC.EMPNO).ENAME := REC.ENAME;
 13      END LOOP;
 14      CLOSE CV_IN;
 15      RETURN RETVAL;
 16    END;
 17  END;
 18  /

Package body created.

SQL> SET SERVEROUTPUT ON;
SQL> /

Package body created.

SQL> DECLARE
  2    L_AA_VAR TYPE_DEF_PKG.EMP_AAT;
  3    CUR_VAR_AA TYPE_DEF_PKG.EMP_RCT;
  4    L_IDX PLS_INTEGER;
  5    L_CNT PLS_INTEGER := 0;
  6  BEGIN
  7    OPEN CUR_VAR_AA FOR SELECT EMPNO, ENAME FROM EMP;
  8    L_AA_VAR := TYPE_DEF_PKG.TBL_FUNC_AA(CUR_VAR_AA);
  9
 10    L_IDX := L_AA_VAR.FIRST;
 11    LOOP
 12      EXIT WHEN L_IDX IS NULL;
 13      DBMS_OUTPUT.PUT_LINE(L_AA_VAR(L_IDX).EMPNO || ' - ' || L_AA_VAR(L_IDX).ENAME);
 14      L_IDX := L_AA_VAR.NEXT(L_IDX);
 15    END LOOP;
 16
 17    DBMS_OUTPUT.PUT_LINE('L_CNT BEFORE LOOP: ' || L_CNT);
 18    FOR REC IN (SELECT * FROM TABLE(L_AA_VAR))
 19      LOOP
 20        L_CNT := L_CNT + 1;
 21         --DBMS_OUTPUT.PUT_LINE(REC.EMPNO);
 22      END LOOP;
 23    DBMS_OUTPUT.PUT_LINE('L_CNT AFTER LOOP: ' || L_CNT);
 24  END;
 25  /
7369 - SMITH
7499 - ALLEN
7521 - WARD
7566 - JONES
7654 - MARTIN
7698 - BLAKE
7782 - CLARK
7788 - SCOTT
7839 - KING
7844 - TURNER
7876 - ADAMS
7900 - JAMES
7902 - FORD
7934 - MILLER
L_CNT BEFORE LOOP: 0
L_CNT AFTER LOOP: 14

PL/SQL procedure successfully completed.

SQL> SELECT *
  2  FROM TABLE(TYPE_DEF_PKG.TBL_FUNC_AA(CURSOR(SELECT EMPNO, ENAME FROM EMP)));
FROM TABLE(TYPE_DEF_PKG.TBL_FUNC_AA(CURSOR(SELECT EMPNO, ENAME FROM EMP)))
           *
ERROR at line 2:
ORA-00902: invalid datatype

SQL> DECLARE
  2    L_AA_VAR TYPE_DEF_PKG.EMP_AAT;
  3    CUR_VAR_AA TYPE_DEF_PKG.EMP_RCT;
  4    L_IDX PLS_INTEGER;
  5    L_CNT PLS_INTEGER := 0;
  6
  7    CUR PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
  8    COLS DBMS_SQL.DESC_TAB;
  9    NCOLS PLS_INTEGER;
 10  BEGIN
 11    OPEN CUR_VAR_AA FOR SELECT EMPNO, ENAME FROM EMP;
 12    L_AA_VAR := TYPE_DEF_PKG.TBL_FUNC_AA(CUR_VAR_AA);
 13
 14    L_IDX := L_AA_VAR.FIRST;
 15    LOOP
 16      EXIT WHEN L_IDX IS NULL;
 17      DBMS_OUTPUT.PUT_LINE(L_AA_VAR(L_IDX).EMPNO || ' - ' || L_AA_VAR(L_IDX).ENAME);
 18      L_IDX := L_AA_VAR.NEXT(L_IDX);
 19    END LOOP;
 20
 21    DBMS_OUTPUT.PUT_LINE('L_CNT BEFORE LOOP: ' || L_CNT);
 22    FOR REC IN (SELECT * FROM TABLE(L_AA_VAR))
 23      LOOP
 24      L_CNT := L_CNT + 1;
 25        --DBMS_OUTPUT.PUT_LINE(REC.EMPNO);
 26      END LOOP;
 27    DBMS_OUTPUT.PUT_LINE('L_CNT AFTER LOOP: ' || L_CNT);
 28
 29    DBMS_SQL.PARSE
 30              (CUR, 'SELECT * FROM TABLE(L_AA_VAR)', DBMS_SQL.NATIVE);
 31    DBMS_SQL.DESCRIBE_COLUMNS(CUR, NCOLS, COLS);
 32    FOR COLIND IN 1 .. NCOLS
 33      LOOP
 34        DBMS_OUTPUT.PUT_LINE(COLS(COLIND).COL_NAME);
 35      END LOOP;
 36    DBMS_SQL.CLOSE_CURSOR(CUR);
 37  END;
 38  /
7369 - SMITH
7499 - ALLEN
7521 - WARD
7566 - JONES
7654 - MARTIN
7698 - BLAKE
7782 - CLARK
7788 - SCOTT
7839 - KING
7844 - TURNER
7876 - ADAMS
7900 - JAMES
7902 - FORD
7934 - MILLER
L_CNT BEFORE LOOP: 0
L_CNT AFTER LOOP: 14
DECLARE
*
ERROR at line 1:
ORA-00904: "L_AA_VAR": invalid identifier
ORA-06512: at "SYS.DBMS_SQL", line 1120
ORA-06512: at line 29

SQL> DECLARE
  2    L_AA_VAR TYPE_DEF_PKG.EMP_AAT;
  3    CUR_VAR_AA TYPE_DEF_PKG.EMP_RCT;
  4
  5    CUR PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
  6    COLS DBMS_SQL.DESC_TAB;
  7    NCOLS PLS_INTEGER;
  8
  9    CUR_EMP SYS_REFCURSOR;
 10  BEGIN
 11    OPEN CUR_VAR_AA FOR SELECT EMPNO, ENAME FROM EMP;
 12    L_AA_VAR := TYPE_DEF_PKG.TBL_FUNC_AA(CUR_VAR_AA);
 13
 14    OPEN CUR_EMP FOR SELECT * FROM TABLE(L_AA_VAR);
 15    CUR := DBMS_SQL.TO_CURSOR_NUMBER(RC => CUR_EMP);
 16
 17    DBMS_SQL.DESCRIBE_COLUMNS(CUR, NCOLS, COLS);
 18    FOR COLIND IN 1 .. NCOLS
 19      LOOP
 20        DBMS_OUTPUT.PUT_LINE(COLS(COLIND).COL_NAME);
 21      END LOOP;
 22    DBMS_SQL.CLOSE_CURSOR(CUR);
 23
 24  END;
 25  /
ATTR_1
ATTR_2

PL/SQL procedure successfully completed.