Пример типа с коллекциями

Например, нужно реализовать настройку в виде нескольких списков (списки товаров и торговых точек, где применяется акция). ID товаров и точек будем хранить в таблице со структурой (Тип сущности {Товар, Точка}, ID). С помощью приведенной ниже методики мы можем хранить оба списка в одном объекте и легко к ним обращаться как к коллекциям. Алгоритм сбора данных инкапсулируется в самом объекте, а выполняется в конструкторе, что позволяет выполнить сбор данных одной строкой кода.
Итак:
Тип строки

create or replace type MY_COLLECTION_ROW is
       object(
          value varchar2(150)
       );

Табличный тип

create or replace type MY_COLLECTION is
       table of MY_COLLECTION_ROW 

Новый тип, содержащий объекты типа MY_COLLECTION

create or replace type MY_COLLECTION_TYPE as object (

       GOODS_LIST                MY_COLLECTION,
       SHOPS_LIST                MY_COLLECTION,

       constructor function MY_COLLECTION_TYPE return self as result,

       member      procedure init_SHOPS_LIST_collection(p_list in out MY_COLLECTION),
       member      procedure init_GOODS_LIST_collection(p_list in out MY_COLLECTION)
)

create or replace type body MY_COLLECTION_TYPE is

       constructor function MY_COLLECTION_TYPE return self as result
       is
       begin
          GOODS_LIST                := new MY_COLLECTION(null); 
          SHOPS_LIST                := new MY_COLLECTION(null);

          init_GOODS_LIST_collection(p_list => GOODS_LIST);
          init_SHOPS_LIST_collection(p_list => SHOPS_LIST);
         return;
       end MY_COLLECTION_TYPE;
       --
       member      procedure init_GOODS_LIST_collection(p_list in out MY_COLLECTION)
       is
         l_line varchar2(150);
         cursor cur is
                    select rownum
                           from dual
                           connect by rownum < 10;
       begin
         open cur;
         loop
           fetch cur into l_line;
           exit when cur%NOTFOUND;
                 p_list.extend;
                 p_list(p_list.last) := MY_COLLECTION_ROW(l_line);
           end loop;
           close cur;
       end init_GOODS_LIST_collection;
       --
       member      procedure init_SHOPS_LIST_collection(p_list in out MY_COLLECTION)
       is
         l_line varchar2(150);
         cursor cur is
                    select rownum
                           from dual
                           connect by rownum < 10;
       begin
         open cur;
         loop
           fetch cur into l_line;
           exit when cur%NOTFOUND;
                 p_list.extend;
                 p_list(p_list.last) := MY_COLLECTION_ROW(l_line);
           end loop;
           close cur;
       end init_SHOPS_LIST_collection;
end;

Обращаемся к данным

declare 
  l_options         MY_COLLECTION_TYPE;
  cursor cur(obj MY_COLLECTION) is select * from table(obj);
  l_tmp_coll        varchar2(150);           
begin
  l_options := new MY_COLLECTION_TYPE();
  open cur(obj => l_options.GOODS_LIST);
  loop
        fetch cur into l_tmp_coll;
        exit when cur%NOTFOUND;
           dbms_output.put_line(l_tmp_coll);
  end loop;
  close cur;
end;