René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Example for multiset

create type a_b_type as object (
  a number,
  b varchar2(10)
);
/

create type a_b_table as table of a_b_type
/
create table a_b_t (
  a number,
  b varchar2(10)
);

insert into a_b_t values (1, 'one');
insert into a_b_t values (2, 'two');
insert into a_b_t values (3, 'three');
insert into a_b_t values (4, 'four');
insert into a_b_t values (5, 'five');
insert into a_b_t values (6, 'six');
insert into a_b_t values (7, 'seven');
insert into a_b_t values (8, 'eight');

commit;
set serveroutput on size 1000000
declare
  v_a_b_table a_b_table;
begin
  select
    cast(
      multiset(select * from a_b_t) as a_b_table
    )
    into v_a_b_table
  from
    dual;

  for r in (
    select 
      a,b
    from
      table(v_a_b_table)
    where a = length(b)-2) loop
      dbms_output.put_line(r.a || ' ' || r.b);
  end loop;
end;
/