Scumm Bar: October 2016

08/10/2016

[Oracle] SQL SELECT cast to table

In Oracle it is possible to cast one type to another simply by using the CAST keyword.

This also applies to collections, meaning that the result of a SELECT can be casted to varray or nested table; in this case it is necessary to add the MULTISET keyword and make sure that all elements in the collection have a valid match.

Eg suppose you have a type:

CREATE TYPE int_list AS TABLE OF NUMBER(9);

You can run a query that selects some integers and cast the result set to int_list:

SELECT
CAST(
    MULTISET(
      SELECT t.int_value
      FROM myTable t
    )
   AS int_list)
FROM dual;