Lesson 1

Throughout this course, you have had the opportunity to execute queries for the various types of object tables.
This module will focus on the Data Manipulation Language (DML)^[1] statements such as INSERT, UPDATE, and DELETE for object tables with and without the reference (REF) operator. DML statements on object tables are similar to statements on normal relational tables. However, there are some subtle differences, which we will review in the following lessons.

When you have completed this module, you will be able to:

1. Define important concepts for modifying object tables
2. Write Structured Query Language (SQL) to insert a row into an object table
3. Insert rows into sub queries
4. Write SQL to update object tables
5. Delete rows using SQL

In PL/SQL programming, the handling of Data Manipulation Language (DML) statements on object tables does share similarities with their use on traditional relational tables, yet there are noteworthy distinctions to consider. Primarily, DML statements (INSERT, UPDATE, DELETE) operate on object tables much like they do on relational tables. For instance, when inserting data, you typically specify values for each column or attribute in the table. In the context of an object table, these attributes correspond to the attributes of the object type that the table is based on. However, when dealing with object tables, one must also consider the object-oriented features of PL/SQL. This includes the instantiation of objects and the possible use of methods or functions associated with the object type. For example, when inserting data into an object table, you might need to create an instance of the object type using the constructor method before inserting it into the table. Furthermore, dealing with nested objects or reference types introduces additional complexity in DML operations on object tables. The handling of references (REFs) to other objects, and the manipulation of nested table and VARRAY data types, require special syntax and considerations that are not typically encountered in standard relational tables. In summary, while the basic principles of DML statements in PL/SQL apply to both object and relational tables, the object-oriented features of PL/SQL introduce additional layers of complexity and functionality when working with object tables. These aspects require a deeper understanding of object types, their methods, and the handling of specialized data structures like nested tables and VARRAYs.

When an exception is raised in a PL/SQL block, normal execution is halted and control is transferred to the exception section. You can never return to the execution section once an exception is raised in that block. In some cases, however, the ability to continue past exceptions is exactly the desired behavior. Consider the following scenario: I need to write a procedure that performs a series of DML statements against a variety of tables (delete from one table, update another, insert into a final table). My first pass at writing this procedure might produce code like the following:

PROCEDURE change_data IS
BEGIN
DELETE FROM employees WHERE ... ;
UPDATE company SET ... ;
INSERT INTO company_history SELECT * FROM company WHERE ... ;
END;

In the next lesson, we will describe some key concepts for modifying object tables.

[1]DML - Data Manipulation Language: INSERT, UPDATE, and DELETE statements are DML statements.