Use COLUMN_VALUE when selecting from scalar table function

Received this question today:

I don’t have a problem to select from collection when collection is based on objects with columns/attributes. What about a collection defined as:

TYPE list_of_numbers_t IS TABLE OF NUMBER;

What would be the column name when you select from the collection?

Short answer:

COLUMN_VALUE

Longer answer: here's a script I used to demonstrate several different features of nested tables. See query at bottom.

CREATE OR REPLACE TYPE list_of_names_t
   IS TABLE OF VARCHAR2 (100);
/

GRANT EXECUTE ON list_of_names_t TO PUBLIC
/

DECLARE
   happyfamily     list_of_names_t := list_of_names_t ();
   children        list_of_names_t := list_of_names_t ();
   grandchildren   list_of_names_t := list_of_names_t ();
   parents         list_of_names_t := list_of_names_t ();
BEGIN
   /* Can extend in "bulk" - 6 at once here */
   happyfamily.EXTEND (6);
   happyfamily (1) := 'Veva';
   happyfamily (2) := 'Chris';
   happyfamily (3) := 'Lauren';
   happyfamily (4) := 'Loey';
   happyfamily (5) := 'Eli';
   happyfamily (6) := 'Steven';
   
   /* Individual extends. */
   children.EXTEND;
   children (children.LAST) := 'Chris';
   children.EXTEND;
   children (children.LAST) := 'Eli';
   children.EXTEND;
   children (children.LAST) := 'Lauren';
   --
   grandchildren.EXTEND;
   grandchildren (grandchildren.LAST) := 'Loey';

   /* Multiset operators on nested tables */
   parents :=
      (happyfamily MULTISET EXCEPT children)
         MULTISET EXCEPT grandchildren;

   FOR l_row IN 1 .. parents.COUNT
   LOOP
      DBMS_OUTPUT.put_line (parents (l_row));
   END LOOP;

   /* Use TABLE operator to apply SQL operations to
      a PL/SQL nested table */

   FOR rec IN (  SELECT COLUMN_VALUE family_name
                   FROM TABLE (happyfamily)
               ORDER BY family_name)
   LOOP
      DBMS_OUTPUT.put_line (rec.family_name);
   END LOOP;
END;
/

And note that as of 12.1, you can use the TABLE operator with associative arrays as well!