In this lesson, we will look at the different ways of inserting data into a nested table. Data can be inserted into a nested statement in two ways:
Use a single Data Manipulation Language (DML) statement to insert data into the parent table and the child table (nested table).
Write two DML statements, the first to insert data into the parent table with an empty record into the nested table, and the second to insert data into the nested table.
With the first technique, the INSERT statement for a nested table consists of specifying the nested table column name and the values for it. For example, let us insert a record into the SALE_HEADER table, which has the DETAIL_NEST column defined as a nested table. The INSERT statement looks like this:
INSERT INTO SALE_HEADER
SELECT 35, REF(C), 55, 1.65, 61.9, 5.25,
to_date('12-dec-99 04:15PM','dd-mon-yy hh:mipm'),
DETAIL_TABLE(NULL, 12, 35.5)
FROM CUSTOMER_OBJ_TABLE C
WHERE CUST_ID = 1;
The statement above inserts NULL, 12, and 35.5 into PRODUCT_ID, ITEM_QTY, and DETAIL_AMOUNT columns within the nested table. With the second technique, you can also insert data into a nested table by creating an empty nested table. Note that the constructor[1] creates an empty nested table as opposed to leaving it NULL. We know this because when the table is queried, the result set shows that the nested table is initialized. Without using the constructor, you cannot refer to the nested table with the THE or TABLE syntax because the nested table will be NULL. For example, let's use the INSERT statement and create an empty nested table record:
INSERT INTO SALE_HEADER
SELECT 35, REF(C), 55, 1.65, 61.9, 5.25,
to_date('12-dec-99 04:15PM','dd-mon-yy hh:mipm'),
DETAIL_TABLE()
FROM CUSTOMER_OBJ_TABLE C
WHERE CUST_ID = 1;
Now that an empty nested table has been created,
let us insert the data into the nested table by using the INSERT statement defined below:
INSERT INTO TABLE (SELECT s.detail_nest FROM
SALE_HEADER S
WHERE s.sale_id = 35)
SELECT REF(P), 1, 55
FROM PRODUCT_OBJ_TABLE P
WHERE product_id = 20;
The above statement first selects the particular record from the SALE_HEADER table, then inserts the data into the nested table by using a SELECT statement. Each row within a nested table has a "hidden" column called nested_table_id. The value of this column is the same for each row within the nested table that corresponds to a single row within the main table. We can view this column by selecting it from the table:
SELECT nested_table_id
FROM THE(select detail_nest FROM SALE_HEADER
where sale_id = 35);
The following diagram describes the syntax for inserting data into a nested table:
Syntax for Inserting Data into Nested Table
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.
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:
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
The following diagram shows an example of inserting data into a nested table:
The INSERT statement with the TABLE keyword
The SELECT statement for selecting a particular record from the parent table
The sub query to select reference and other column data
The sub query to select reference and other column data
The sub query to select reference and other column data
INSERT INTO TABLE (SELECT s.detail_nest FROM
SALE_HEADER s
WHERE s.sale_id =36)
SELECT REF(P), 200, 56
FROM PRODUCT_OBJ_TABLE p
WHERE product_id =21;
The SELECT statement for selecting a particular record from the parent table
Inserting Data into Nested Table (Example)
INSERT INTO TABLE (SELECT s.detail_nest FROM
SALE_HEADER s
WHERE s.sale_id =36)
SELECT REF(P), 200, 56
FROM PRODUCT_OBJ_TABLE p
WHERE product_id =21;
Inserting data into a nested table example
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-5
The sub query to select reference and other column data
In the next lesson, you will learn more about inserting data into a varray.
[1]Constructor: Every time a user-defined data type is created, Oracle generates a method called the constructor method, which is a system-defined method. When invoked, the constructor method creates a new object based on the specification of the object type. Oracle names the new constructor method the same name as the new object type. The parameters are the same as the attributes named within the object type definition.
