this is a good workaround: the most important thing is the correct deserialization of the arrays. i'll try it! :) thanks!
On Thursday, August 30, 2012 5:21:42 PM UTC+2, Lukas Eder wrote: > > 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] <javascript:>>: > > 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; > > >
