Question:  I want to see an example of a nested table insert.  How do you insert into a nested table?

Answer:  Using the Oracle nested table construct, you can see hiw the insert is done for an Oracle nested table.

Example insert for nested tables

Now we insert rows into the nested table.

Note the use of the full_mailing_address_type reference for the ADT and the specification of the repeating groups of previous employers.

insert into emp
values
(
'Burleson',
full_mailing_address_type('7474 Airplane Ave.','Rocky Ford','NC','27445'),
prior_employer_name_arr(
employer_name('IBM'),
employer_name('ATT'),
employer_name('CNN')
)
);
insert into emp
values
(
'Lavender',
full_mailing_address_type('7474 Bearpond Ave.','Big Lick','NC','17545'),
prior_employer_name_arr(
employer_name('Oracle'),
employer_name('Sybase'),
employer_name('Computer Associates')
)
);

Next, we perform the select SQL. Note that we can select all of the repeating groups with a single reference to the prior_employers column.

select
p.prior_employers
from
emp p
where
p.last_name = 'Burleson';
PRIOR_EMPLOYERS(E_NAME)
--------------------------------------------------------------------------------
PRIOR_EMPLOYER_NAME_ARR(EMPLOYER_NAME('IBM'), EMPLOYER_NAME('ATT'), EMPLOYER_NAM
E('CNN'))

This output can be difficult to interpret because of the nature of the repeating groups. In the example below, we use a new BIF called table that will flatten-out the repeating groups, re-displaying the information.

column l_name heading "Last Name" format a20;

SELECT
emp.last_name l_name,
prior_emps.*
FROM
emp emp,
table(p.prior_employers) prior_emps
WHERE
p.last_name = 'Burleson';

Here we see a flattened output from the query, and the single information is replicated onto each table row.

Last Name E_NAME
-------------------- ----------------------------------------
Burleson IBM
Burleson ATT
Burleson CNN

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.