PL/SQL   «Prev 

Inserting Data into Nested Table [Syntax]

INSERT INTO TABLE (SELECT <nested table> FROM
   <parent table name><table alias>
   WHERE <clause>)
SELECT <column values>
FROM <reference table name><reference table alias>
WHERE <clause>;
	
Inserting data into nested table syntax

Location 1 The INSERT statement with the TABLE keyword
Location 2 The SELECT statement for selecting a particular record from the parent table
Location 3 The sub query to select reference and other column data

Inserting Records into Nested Table

You can insert records into a nested table by using the constructor methods for its datatype. For the Animals column, the datatype is ANIMALS_NT; thus, you will use the ANIMALS_NT constructor method. The ANIMALS_NT type, in turn, uses the ANIMAL_TY datatype.
As shown in the following example, inserting a record into the BREEDER table requires you to use both the ANIMALS_NT and ANIMAL_TY constructor methods. In the example, three animals are listed for the breeder named Jane James.

insert into BREEDER values
('JANE JAMES',
ANIMALS_NT(
ANIMAL_TY('DOG', 'BUTCH', '31-MAR-2017'),
ANIMAL_TY('DOG', 'ROVER', '05-JUN-2017'),
ANIMAL_TY('DOG', 'JULIO', '10-JUN-2017')
));

This insert command first specifies the name of the breeder:
insert into BREEDER values
('JANE JAMES',

Next, the value for the Animals column must be entered. Since the Animals column uses the ANIMALS_NT nested table, the ANIMALS_NT constructor method is invoked in this line:
ANIMALS_NT(

The ANIMALS_NT nested table uses the ANIMAL_TY datatype, so the ANIMAL_TY constructor method is invoked for each record inserted:
ANIMAL_TY('DOG', 'BUTCH', '31-MAR-2017'),
ANIMAL_TY('DOG', 'ROVER', '05-JUN-2017'),
ANIMAL_TY('DOG', 'JULIO', '10-JUN-2017')
The final two parentheses complete the command, closing the call to the ANIMALS_NT constructor method and closing the list of inserted values:
	));

If you do not already know the datatype structure of the table, you need to query the data dictionary before you can query the table.
First, describe BREEDER or query USER_TAB_COLUMNS to see the definitions of the columns:
select Column_Name,
Data_Type
from USER_TAB_COLUMNS
where Table_Name = 'BREEDER';
COLUMN_NAME                    DATA_TYPE
----------------------------- ------------
BREEDERNAME                    VARCHAR2
ANIMALS                        ANIMALS_NT

The USER_TAB_COLUMNS output shows that the Animals column uses the ANIMALS_NT datatype. To verify that the ANIMALS_NT datatype is a collector, check USER_TYPES:
select TypeCode, Attributes
from USER_TYPES
where Type_Name = 'ANIMALS_NT';
TYPECODE                ATTRIBUTES
---------------------- ----------
COLLECTION               0