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!


Comments

  1. One can also give the TABLE expression an alias and reference the COLUMN_VALUE as VALUE(alias).

    Gerard

    ReplyDelete

Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Recommendations for unit testing PL/SQL programs

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts