Database Reference
In-Depth Information
EODA@ORA12CR1> select d.dname, e.empno, ename, deptno
2 from dept_and_emp d, table(d.emps) e
3 where d.deptno in ( 10, 20 );
DNAME EMPNO ENAME DEPTNO
-------------- ---------- ---------- ----------
ACCOUNTING 7782 CLARK 10
ACCOUNTING 7839 KING 10
ACCOUNTING 7934 MILLER 10
ACCOUNTING 1234 NewEmp 10
RESEARCH 7369 SMITH 20
RESEARCH 7566 JONES 20
RESEARCH 7876 ADAMS 20
RESEARCH 7902 FORD 20
8 rows selected.
That is the basic syntax of how to query and modify nested tables. You will find that you often need to un-nest
these tables as we just did, especially in queries, to make use of them. Once you conceptually visualize the “virtual
table per row” concept, working with nested tables becomes much easier.
Previously I stated, “We must always join; we cannot query the
EMP
data alone,” but then I followed that up with
a caveat: “You can if you really need to.” It is not documented heavily; use this approach
only
as a last ditch method.
Where it will come in most handy is if you ever need to mass update the nested table (remember, you would have
to do that through the
DEPT
table with a join). There is an underdocumented hint (it is mentioned briefly and not
fully documented),
NESTED_TABLE_GET_REFS
, which is used by various tools (including the deprecated
EXP
and
IMP
utilities) to deal with nested tables. It is also a way to see a little more about the physical structure of the nested tables.
If you use this hint, you can query to get some “magical” results. The following query is what
EXP
(a data unload
utility) uses to extract the data from this nested table:
EODA@ORA12CR1> SELECT /*+NESTED_TABLE_GET_REFS+*/
2 NESTED_TABLE_ID,SYS_NC_ROWINFO$ FROM "EODA"."EMPS_NT";
NESTED_TABLE_ID SYS_NC_ROWINFO$(EMPNO, ENAME, JOB, MGR, HIREDATE,
-------------------------------- --------------------------------------------------
EF6CDA23E32D315AE043B7D04F0AA620 EMP_TYPE(7782, 'CLARK', 'MANAGER', 7839, '09-JUN-8
1', 2450, 100)
EF6CDA23E32D315AE043B7D04F0AA620 EMP_TYPE(7839, 'KING', 'PRESIDENT', NULL, '17-NOV-
81', 5000, 100)
...
Well, this is somewhat surprising, if you describe this table:
EODA@ORA12CR1> desc emps_nt
Name Null? Type
----------------------------- -------- --------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
