Restrictions on a collection derived table

The following restrictions apply to querying a collection derived table that is a virtual table:
  • It cannot be the target of INSERT, DELETE, or UPDATE statements.
  • It cannot be the underlying table of any cursors or views that can be updated.
  • It does not support ordinality. For example, it does not support the following statement: 
    select name, order_in_list from table(select children
   from parents where parents.id = 1001)
   with ordinality(order_in_list);
  • It is an error if the underlying collection expression of the collection derived table evaluates to a null value.
  • It cannot reference columns of tables that are referenced in the same FROM clause. For example, it does not support the following statement because the collection derived table table(parents.children) refers to the table parents, which is referenced in the FROM clause: 
    select count(distinct c_id) from parents,
   table(parents.children) c_table(c_name, c_id)
   where parents.id = 1001
  • The database server must be able to statically determine the type of the underlying collection expression. For example, the database server cannot support: TABLE(?)
  • The database server cannot support a reference to a host variable without casting it to a known collection type. For example, rather than specifying TABLE(:hostvar), you must cast the host variable: 
    TABLE(CAST(:hostvar AS type))
TABLE(CAST(? AS type))
  • It will not preserve the order of rows in the list if the underlying collection is a list.