Select query from nested table of references

Question

I have two object-relational tables: person_table and account_table, built out of objects person and account. An account row has a nested table for all the people sharing this account, its type is 'customer_list'.

create type person as object(
  id integer,
  name varchar2,
  phone varchar2
);


create type customer_list as table of ref person;

create type account as object(
  accid integer,
  owned_by customer_list,
  balance Integer
);

create table account_table of account;

create table person_table of person;

I want to select all the accounts owned by a specific person, given the person's id. How do I browse through all the nested tables of account, what is the query ? I tried unsuccessful queries.

For example

select a.*
from account_table a
where table(a.owned_by) = (select ref(p) from person_table p where p.id=id_given);

Thank you

Answer 1

Here is my test data:

SQL> select * from person_table;

        ID NAME                           PHONE
---------- ------------------------------ ------------
        11 MR KNOX                        07000700811
        22 SAM-I-AM                       07000700822

SQL> select * from account_table;

     ACCID
----------
OWNED_BY
--------------------------------------------------------------------------------
   BALANCE
----------
       123
CUSTOMER_LIST(00002202084B026AE209637509E050007F010047FD4B026AE209627509E050007F010047FD
            , 00002202084B026AE209647509E050007F010047FD4B026AE209627509E050007F010047FD)
      9900

       345
CUSTOMER_LIST(00002202084B026AE209637509E050007F010047FD4B026AE209627509E050007F010047FD)
        30


SQL>

Sam-I-Am and Mr Knox are joint owners of the first account and Mr Knox is the sole owner of the second account. To find the accounts owned by Mr Knox we can run this query, which looks up the REF from person_table...

SQL> select acct.accid
  2        , acct.balance
  3        , deref(value(ob)).name as owned_by
  4        , deref(value(ob)).phone as ntact_noco
  5  from account_table acct
  6       cross join table(acct.owned_by) ob
  7  where ob.column_value = ( select ref(p) pref
  8                            from person_table p
  9                            where p.name = 'MR KNOX')
 10  /  

     ACCID    BALANCE OWNED_BY                       NTACT_NOCO
---------- ---------- ------------------------------ ------------
       123       9900 MR KNOX                        07000700811
       345         30 MR KNOX                        07000700811

SQL> 

Alternatively, we can just look up the person with the DEREF syntax:

SQL> select acct.accid
  2         , acct.balance
  3         , deref(value(ob)).name as owned_by
  4         , deref(value(ob)).phone as contact_no
  5  from account_table acct
  6       cross join table(acct.owned_by) ob
  7  where  deref(value(ob)).id = 11 
  8  /

     ACCID    BALANCE OWNED_BY                       CONTACT_NO
---------- ---------- ------------------------------ ------------
       123       9900 MR KNOX                        07000700811
       345         30 MR KNOX                        07000700811

SQL>