Read data from Nested Table in Oracle

SQL> CREATE OR REPLACE TYPE t_emp_no_name_obj AS OBJECT
  2  (
  3     NO NUMBER,
  4     NAME VARCHAR2 (100)
  5  );
  6  /

Type created.

SQL>
SQL> CREATE OR REPLACE TYPE t_emp_no_name_list IS TABLE OF t_emp_no_name_obj;
  2  /

Type created.

SQL>
SQL> SET SERVEROUTPUT ON;
SQL>
SQL> CREATE OR REPLACE FUNCTION f_emp_names
  2     RETURN t_emp_no_name_list
  3  AS
  4     v_emp_no_name_list   t_emp_no_name_list := t_emp_no_name_list ();
  5  BEGIN
  6     v_emp_no_name_list.DELETE ();
  7
  8     SELECT CAST (
  9               MULTISET (SELECT empno, ename FROM emp) AS t_emp_no_name_list)
 10       INTO v_emp_no_name_list
 11       FROM DUAL;
 12
 13     RETURN v_emp_no_name_list;
 14  EXCEPTION
 15     WHEN OTHERS
 16     THEN
 17        RETURN NULL;
 18        DBMS_OUTPUT.put_line ('Oracle Error Code=' || SQLCODE);
 19        DBMS_OUTPUT.put_line ('Oracle Error Message=' || SQLERRM);
 20  END f_emp_names;
 21  /

Function created.

SQL>
SQL> SELECT f_emp_names FROM DUAL;

F_EMP_NAMES(NO, NAME)
------------------------------------------------------------------------------------------------------------------------------------------------------
T_EMP_NO_NAME_LIST(T_EMP_NO_NAME_OBJ(7369, 'RAJ'), T_EMP_NO_NAME_OBJ(7499, 'ALLEN'), T_EMP_NO_NAME_OBJ(7521, 'WARD'), T_EMP_NO_NAME_OBJ(7566, 'JONES')
, T_EMP_NO_NAME_OBJ(7654, 'MARTIN'), T_EMP_NO_NAME_OBJ(7698, 'BLAKE'), T_EMP_NO_NAME_OBJ(7782, 'CLARK'), T_EMP_NO_NAME_OBJ(7788, 'SCOTT'), T_EMP_NO_NA
ME_OBJ(7839, 'KING'), T_EMP_NO_NAME_OBJ(7844, 'TURNER'), T_EMP_NO_NAME_OBJ(7876, 'ADAMS'), T_EMP_NO_NAME_OBJ(7900, 'JAMES'), T_EMP_NO_NAME_OBJ(7902, '
FORD'), T_EMP_NO_NAME_OBJ(7934, 'MILLER'))


SQL>
SQL> SELECT * FROM TABLE (SELECT f_emp_names FROM DUAL);

        NO NAME
---------- ----------------------------------------------------------------------------------------------------
      7369 RAJ
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

        NO NAME
---------- ----------------------------------------------------------------------------------------------------
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

SQL>