This page discusses variations of inserting in Oracle using the PL/SQL Programming language.
Inserting Records into the Varying Array
When a datatype is created, the database automatically creates a method called a constructor method for the datatype. You need to use the constructor method when inserting records into columns that use an abstract datatype. Since a varying array is an abstract datatype, you need to use constructor methods to insert records into tables that use varying arrays. Furthermore, since the varying array is itself an abstract datatype, you may need to nest calls to multiple constructor methods to insert a record into a table that uses a varying array. The columns of the BORROWER table are Name and Tools, the last of which is a varying array using the TOOLS_VA datatype. The following command will insert a single record into the BORROWER table. In this example, the record will have a
single Name column value and three Tools values.
insert into BORROWER values
('JED HOPKINS',
TOOLS_VA('HAMMER','SLEDGE','AX'));
Oracle PL/SQL Programming
1) Insert into object tables, which have columns with associated object types. In such case, we must specify the name of the object type and its attributes within the INSERT statement.
2) Here is an example of the code for method 1.
3) Insert into object tables, which are associated with a nested table. In such a case we must select the reference from the other table, that is, the table to which it is referenced. When using this method, values for all the columns within the main table must be provided, even if they are NULL.
4) Here is an example of the code for method 2.
5) Insert into object tables with reference to other objects can be done as a sub-query for the reference column only, then listing the data for the other columns
6) Here is an example of the code for method 3.