Database Reference
In-Depth Information
7654 MARTIN
7698 BLAKE
7900 JAMES
7 rows selected.
EODA@ORA12CR1> update /*+ nested_table_get_refs */ emps_nt set ename = initcap(ename);
14 rows updated.
EODA@ORA12CR1> select /*+ nested_table_get_refs */ empno, ename
2 from emps_nt where ename like '%a%';
EMPNO ENAME
---------- ----------
7782 Clark
7876 Adams
7521 Ward
7654 Martin
7698 Blake
7900 James
6 rows selected.
Again, this is not a thoroughly documented and supported feature. It has a specific functionality for
EXP
and
IMP
to work. This is the only environment it is assured to work in. Use it at your own risk, and resist putting it into
production code. In fact, if you find you
need to use it
, then by definition you didn't mean to use a nested table at all! It
is the wrong construct for you. Use it for one-off fixes of data or to see what is in the nested table out of curiosity.
The supported way to report on the data is to un-nest it like this:
EODA@ORA12CR1> select d.deptno, d.dname, emp.*
2 from dept_and_emp D, table(d.emps) emp
3 /
This is what you should use in queries and production code.
Nested Table Storage
We have already seen some of the storage of the nested table structure. In this section, we'll take an in-depth look at
the structure created by Oracle by default and what control we have over it. Working with the same
CREATE
statement
as before
EODA@ORA12CR1> create table dept_and_emp
2 (deptno number(2) primary key,
3 dname varchar2(14),
4 loc varchar2(13),
5 emps emp_tab_type
6 )
7 nested table emps store as emps_nt;
Table created.
EODA@ORA12CR1> alter table emps_nt add constraint
2 emps_empno_unique unique(empno)
3 /
Table altered.
