René Nyffenegger's collection of things on the web | |
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
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; / |