Зачем нужны функции возвращаюшие коллекции в sql контексте?

Недавно узнал что можно в запросе sql вернуть коллекцию, например вот так:
select cast (multiset (select поле from таблица) as тип коллекции) from dual
Зачем это нужно в sql, если работать с коллекциями можно в pl/sql коде?

Ответ

В вашем примере cast функция с ключевым словом multiset конвертирует набор реляционных данных в sql коллекцию. Основное назначение манипуляция данных в таблицах с колонками объявлеными как коллекции.
Другое применение - маппинг классической схемы с реляционной моделью данных с объектно-реляционной посредством объектных представлений.
Небольшой пример:
create or replace type myItemType as object (id number, name varchar(32)); / --Type MYITEMTYPE compiled
create type myItemArrayType as table of myItemType; / --Type MYITEMARRAYTYPE compiled
create table myItems (items myItemArrayType) nested table items store as items_table; / --Table MYITEMS created.
-- обычное занесение одного значения insert into myItems values (myItemArrayType(myItemType(0, 'item 0'))); --1 row inserted.
-- заполнене коллекции из реляционной таблицы (вместо dual здесь одна или несколько таблиц) insert into myItems select cast(multiset( select level, 'item ' || level from dual connect by level <= 5 ) as myItemArrayType) from dual ; --1 row inserted.
select items.* from myItems, table(items) items;
ID NAME -- ------ 0 item 0 1 item 1 2 item 2 3 item 3 4 item 4 5 item 5
Вывод - item 0 из первой строчки, item 1 - item 5 из второй.
Предыдущий ответ Вопрос был изменён и в первоночальной версии он был о применении методов для работы с коллекциями в sql выражениях.
Mетоды для работы с коллекциями, не могут быть исползованы в SQL выражениях. В документации Oracle: A collection method invocation can appear anywhere that an invocation of a PL/SQL subprogram of its type (function or procedure) can appear, except in a SQL statement
PS Может вы имеете в виду что-то другое под определением "методы для работы с коллекциями"? Привидите пример.