Kacper Kacper - 19 days ago 5
SQL Question

How to select data as a table type?

I'd like to build denormalised view that handles reference data in table type.

create table reftab (id number, name varchar2(40), details varchar2(1000));
/
create table basictab (id number, name varchar2(300), contract varchar2 (20));
/
create or replace type reftype is object (id number, name varchar2(40), details varchar2(1000));
/
create or replace type reftypetab as table of reftype;
/
insert into basictab values (1, 'aaa', 'c1');
insert into basictab values (2, 'aab', 'c1');
insert into basictab values (3, 'aaa', 'c2');
insert into basictab values (4, 'aaa', 'c3');

insert into reftab values (1, 'asd', 'aaa');
insert into reftab values (1, 'asg', 'ass');
insert into reftab values (1, 'ash', 'add');
insert into reftab values (1, 'asf', 'agg');
insert into reftab values (3, 'asd', 'aaa');
insert into reftab values (3, 'ad', 'aa');
insert into reftab values (4, 'asd', 'aaa');
insert into reftab values (4, 'as', 'a');
insert into values (4, 'ad', 'aa');
/


With such data I'd like to have view that contains 4 rows of
basictab
with additional column that is
reftypetab
and contains all ref data joined on
id
.

I know I can obtain it by:

CREATE OR REPLACE FUNCTION pipef (p_id IN NUMBER) RETURN reftypetab PIPELINED AS
BEGIN
FOR x IN (select * from reftab where id = p_id) LOOP
PIPE ROW(reftype(x.id, x.name, x.details));
END LOOP;

RETURN;
END;
/

SELECT id, pipef(id)
FROM reftab
group BY id;
/


but is there any better way without function to get the result?

Answer

Your current set-up gets:

SELECT id, pipef(id) as result
FROM  reftab
group BY id;

        ID RESULT(ID, NAME, DETAILS)                                                                                               
---------- ------------------------------------------------------------------------------------------------------------------------
         1 REFTYPETAB(REFTYPE(1, 'asd', 'aaa'), REFTYPE(1, 'asg', 'ass'), REFTYPE(1, 'ash', 'add'), REFTYPE(1, 'asf', 'agg'))      
         4 REFTYPETAB(REFTYPE(4, 'asd', 'aaa'), REFTYPE(4, 'as', 'a'), REFTYPE(4, 'ad', 'aa'))                                     
         3 REFTYPETAB(REFTYPE(3, 'asd', 'aaa'), REFTYPE(3, 'ad', 'aa'))                                                            

You could use the collect() function to simplify that:

select id, cast(collect(reftype(id, name, details)) as reftypetab) as result
from reftab
group by id;

        ID RESULT(ID, NAME, DETAILS)                                                                                               
---------- ------------------------------------------------------------------------------------------------------------------------
         1 REFTYPETAB(REFTYPE(1, 'asd', 'aaa'), REFTYPE(1, 'asf', 'agg'), REFTYPE(1, 'ash', 'add'), REFTYPE(1, 'asg', 'ass'))      
         3 REFTYPETAB(REFTYPE(3, 'asd', 'aaa'), REFTYPE(3, 'ad', 'aa'))                                                            
         4 REFTYPETAB(REFTYPE(4, 'asd', 'aaa'), REFTYPE(4, 'ad', 'aa'), REFTYPE(4, 'as', 'a'))                                     

If you want information from basictab as well you can use a multiset operator:

select bt.id, bt.name,
  cast(multiset(select reftype(rt.id, rt.name, rt.details)
    from reftab rt where rt.id = bt.id) as reftypetab) as result
from basictab bt;

        ID NAME       RESULT(ID, NAME, DETAILS)                                                                                               
---------- ---------- ------------------------------------------------------------------------------------------------------------------------
         1 aaa        REFTYPETAB(REFTYPE(1, 'asd', 'aaa'), REFTYPE(1, 'asg', 'ass'), REFTYPE(1, 'ash', 'add'), REFTYPE(1, 'asf', 'agg'))      
         2 aab        REFTYPETAB()                                                                                                            
         3 aaa        REFTYPETAB(REFTYPE(3, 'asd', 'aaa'), REFTYPE(3, 'ad', 'aa'))                                                            
         4 aaa        REFTYPETAB(REFTYPE(4, 'asd', 'aaa'), REFTYPE(4, 'as', 'a'), REFTYPE(4, 'ad', 'aa'))                                     
Comments