Slide 10.6: Nested tables (cont.)

Nested Tables (Cont.)

Qualifiers are required for object attribute references.

Question: Display all their social security numbers of dependents of employee 101.

SQL> SELECT  ssno  FROM  TABLE( employees.dependents ) 
  2    WHERE  employees.eid = 101; 

SELECT  ssno  FROM  TABLE(employees.dependents)
                          *
ERROR at line 1:
ORA-00904: "EMPLOYEES"."DEPENDENTS": invalid identifier

SQL> SELECT  ssno  FROM 
  2    TABLE( SELECT  e.dependents  FROM  employees e 
  3             WHERE  e.eid = 101 ) d; 

      SSNO
----------
 322456776
 123822332

There are two ways to retrieve from a table of tables:

unnesting via table products and
nested cursors.

Unnesting via Table Products
Q: Retrieve ssno column values from all the dependents of all employees.

Each row in the first table is matched with each row of its own collection column value (converted to a table).
To see a row for an employee with no dependents, with a null ssno, add a ‘+’ to the dependents side.

SQL> SELECT  e.eid, d.ssno 
  2    FROM  employees e, TABLE( e.dependents )(+) d; 

       EID       SSNO
---------- ----------
       101  322456776
       101  123822332
       102  565534555