Inserting Row into Object Table

In this lesson, you will learn techniques for writing DML statements to insert records into object tables. You will also have an opportunity to write the statements on your own. For the purposes of this module, we will insert NULL into the columns for object tables that include varrays or nested tables, because the next module is dedicated to using DML statements on those types of tables.

Let us begin by looking at the INSERT statements for two basic types of object tables:

1. Object tables with associated object types
2. Object tables with references to other objects

In PL/SQL, both object tables and references to objects offer ways to store and manage complex data structures, but they differ significantly in their approach:

1. Object tables with associated object types:
   • Structure: These tables store individual objects as rows. Each row represents an instance of an "object type," which defines the attributes (columns) and methods (functions) associated with the object.
   • Benefits:
     • Encapsulates data and behavior together, promoting data integrity and code reusability.
     • Enables object-oriented features like inheritance and polymorphism within the database.
   • Example:
     

     CREATE TYPE customer_obj AS OBJECT (
       id NUMBER,
       name VARCHAR2(50),
       -- methods for the object
       get_greeting() VARCHAR2
     );
     /
     
     CREATE TABLE customers OF customer_obj;
     

2. Object tables with references to other objects:
   • Structure: These tables use a special column called a "REF" column to store references to existing objects in another table. The referenced object can be of a different type entirely.
   • Benefits:
     • Enables flexible relationships between objects stored in different tables.
     • Reduces data redundancy by referencing existing objects instead of duplicating their data.
   • Example:
     

     CREATE TABLE orders (
       id NUMBER PRIMARY KEY,
       customer_ref REF customer_obj
     );
     
     CREATE TABLE customers (
       id NUMBER PRIMARY KEY,
       name VARCHAR2(50)
     );
     

Key differences:

• Data Storage: Object tables store complete objects as individual rows, while reference tables only store references to existing objects.
• Object-Oriented Features: Object tables with their associated object types directly support object-oriented features like inheritance and polymorphism. References, on the other hand, primarily establish relationships between objects but do not inherently offer these features.
• Flexibility: References offer greater flexibility in establishing relationships between objects stored in different tables, while object tables are primarily used for self-contained objects.

Choosing between these approaches depends on your specific needs. If you require object-oriented features and encapsulation, object tables with associated object types are ideal. However, if you need to establish flexible relationships between objects potentially stored in different tables, object tables with references are better suited.

For object tables with associated object types, you must mention the name of the object type within the INSERT statement. For example, let's look at CUSTOMER_OBJ_TABLE. The FULL_ADDRESS column is based on the ADDRESS_TYPE object type. An example of an INSERT statement looks like this:

INSERT INTO customer_obj_table 
VALUES (customer_type (26 ,'Lester' ,'Lee' ,
address_type('197 Betty Lane', NULL, 'Hollywood' ,
  'CA' ,'10293'  ),
NULL, '10-JUN-99','HENRY'));

Now let us insert a record into an object table that has a reference to another object. To do this, the INSERT statement must include a SELECT statement, because we must query on the reference of the other object to store that association within this table. For example, the PET_CARE_LOG_OBJ_TABLE table is based on the PET_CARE_OBJ_TYPE object type, which in turn, has a reference to the PRODUCT_TYPE object. This object is maintained within the PRODUCT_OBJ_TABLE table. An example of an INSERT statement looks like the following:

INSERT INTO pet_care_log_obj_table
SELECT REF(p), to_date('15-JAN-00 12:30PM',
  'dd-mon-yy hh:miPM') ,
'JANET', 'New puppy needs shots.', to_date
  ('16-JAN-00 01:30PM','dd-mon-yy hh:miPM')
FROM product_obj_table p
WHERE product_id = 22;

In this example, we query the reference within the PRODUCT_OBJ_TABLE table for a particular PRODUCT_ID. We also include the values for other columns. Notice the use of the table alias for inserting records for reference-based object tables.
Now that you have seen a couple of techniques to insert rows into object tables, apply them through the following evaluative simulation, which requires that you write two INSERT statements. The first INSERT statement will insert a record into the CUSTOMER_OBJ_TABLE table. The second INSERT statement will insert a record into PET_CARE_LOG_OBJ_TABLE.
With SQL*Plus, connect to the database by using PETSTORE as the User Name, GREATPETS as the Password, and MYDB as the Host String. Approve the entries. If all three Variations to Insert fields are not filled in, fill in the boxes before clicking OK.

Starting with Oracle9i Database Release 2, you can also perform a record-level insert, simplifying the above INSERT statement into nothing more than this:

INSERT INTO rain_forest_history
(country_code, analysis_date, size_in_acres, species_lost)
VALUES rain_forest_rec;

The PL/SQL extension to the SQL INSERT statement lets you specify a record name in the values_clause of the single_table_insert instead of specifying a column list in the insert_into_clause. Effectively, this form of the INSERT statement inserts the record into the table; actually, it adds a row to the table and gives each column of the row the value of the corresponding record field.

dml_table_expression_clause Typically a table name. For complete information, see Oracle Database SQL Language Reference.
t_alias An alias for dml_table_expression_clause.
values_clause record: Name of a record variable of type RECORD or %ROWTYPE. record must represent a row of the item explained by dml_table_expression_clause. That is, for every column of the row, the record must have a field with a compatible data type. If a column has a NOT NULL constraint, then its corresponding field cannot have a NULL value.
The following series of images offers guidelines and examples on variations for inserting records into object tables:

This page discusses variations of inserting in Oracle using the PL/SQL Programming language.

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'));

In the next lesson, we will write an INSERT statement using a sub query.