I'm afraid, this might not work. jOOQ does not yet formally support MULTISET types, nor casts to Oracle ARRAY types. A workaround for this would be to create a view, such as
------------------------------------------------ create or replace view v as select a.id, cast(multiset(select * from b where b.a_id = a.id) as ARRAY_B) array from a; ------------------------------------------------ And then let the code generator generate this view. The generated view would then correctly contain a V.ID NUMBER column and an V.ARRAY ARRAY_B column. I'm not sure if plain SQL would work either, in this case, as jOOQ doesn't have the necessary information to automatically deserialise the ARRAY_B type. 2012/8/30 Stanislas Nanchen <[email protected]>: > Hi! > > I'm currently trying to reproduce in jOOQ some of the queries we have in a > big project > and i am trying to find out how to write a cast(multiset(<Select>) as > <type>) query. > We use these kinds of queries to retrieve parent rows with arrays of > children rows. > > How do I do that? > Thanks for your help. > > stan. > > example: > > create table A ( > id number, > primary key (id)); > > create table B ( > a_id number, > seq number, > primary key(a_id, seq), > foreign key(a_id) references A(id) ); > > create or replace type OBJECT_B is object( > a_id number, > seq number > ); > / > > create or replace type ARRAY_B is table of OBJECT_B; > / > > insert into a values (1); > > insert into b values (1, 1); > insert into b values (1, 2); > > commit; > > select a.id, cast(multiset(select * from b where b.a_id = a.id) as ARRAY_B) > from a; >
