How can I unnest a collection of nested tables in a query?

Question

I have a procedure that accepts an input similar to parent_arr shown below as input from the application layer via ODP.Net. In the first step of the procedure, I store data from the array in a global temporary table so I can proceed with several following steps using set logic rather than pl/sql loops. As long as the array has only a single member of parent_typ, everything is fine. However, when there is more than one member, I get ORA-01427, single row query returns more than one row. The query below returns two collections. I need to un-nest both collections in a single sql statement that will display child.name and child.value. How can that be done?

Sample objects

create type child_typ is object( name varchar2(100), value number );
create type child_arr is table of dropme_child_typ;
create type parent_typ is object( pname varchar2(100), child dropme_child_arr );
create type parent_arr is table of dropme_parent_typ;

The query below will throw ORA-01427

select * from table(
    select child
    from table( parent_arr(
        parent_typ( 'TEST1',
            child_arr(
                child_typ( 'C1', 1 ),
                child_typ( 'C2', 2 ) ) ),
        parent_typ( 'TEST2',
            child_arr(
                child_typ( 'C3', 3 ),
                child_typ( 'C4', 4 ) ) ) ) ) );

This query works, but returns a column of object child_arr

select child
from table( parent_arr(
    parent_typ( 'TEST1',
        child_arr(
            child_typ( 'C1', 1 ),
            child_typ( 'C2', 2 ) ) ),
    parent_typ( 'TEST2',
        child_arr(
            child_typ( 'C3', 3 ),
            child_typ( 'C4', 4 ) ) ) ) );

This query fails because I can't access values in "child"

select child.name, child.value from
 table( parent_arr(
     parent_typ( 'TEST1',
         child_arr(
             child_typ( 'C1', 1 ),
             child_typ( 'C2', 2 ) ) ),
     parent_typ( 'TEST2',
         child_arr(
             child_typ( 'C3', 3 ),
             child_typ( 'C4', 4 ) ) ) ) );

Please tell me there is a way to do this without using a pl/sql loop (that is the only way I've been able to succeed so far). Speed is of the utmost importance. I tried using a forall statement to loop through memebers of the parent_arr, but it throws a bulk in-bind error.

score 1 · Answer 1 · answered Feb 16 '11 at 09:25

1

You could use a lateral join to unnest your child object:

SQL> WITH my_data AS (
  2     SELECT pname, child
  3       FROM TABLE(parent_arr(parent_typ('TEST1',
  4                                        child_arr(child_typ('C1', 1),
  5                                                  child_typ('C2', 2))),
  6                             parent_typ('TEST2',
  7                                        child_arr(child_typ('C3', 3),
  8                                                  child_typ('C4', 4)))))
  9  )
 10  SELECT my_data.pname, child.name, child.value
 11    FROM my_data, table(my_data.child) child;

PNAME    NAME       VALUE
-------- ----- ----------
TEST1    C1             1
TEST1    C2             2
TEST2    C3             3
TEST2    C4             4

It is a form of outer join where you join the parent with its children.

answered Feb 16 '11 at 09:25

Vincent Malgrat

66,725
9
119
171

Works. Many thanks. I was pursuing cast( multiset( ... this morning and I thought for sure that would work. – Thomas Gnade Feb 16 '11 at 11:53
I just ran explain plan on that simple statement, and the cost is extremely high. I'll have to use this and the pl/sql loop and trace to see which is more cost-effective. Thanks for giving me an alternative solution! – Thomas Gnade Feb 16 '11 at 11:58
@Thomas: the cost is high because Oracle doesn't have any stats on these objects so it makes guesses on the number of elements (in oracle 10.2.0.3, 8168 parents each with 8168 children). In this case the cost is not a good indication of the expected response time of the query. I would think that this SQL would perform better than a PL/SQL loop, still, it would be interesting if you could post the result of your benchmark. – Vincent Malgrat Feb 16 '11 at 13:33

How can I unnest a collection of nested tables in a query?

1 Answers1