Lesson 1

Modifying Nested Tables and Varrays

In this module, we will look at the different ways of inserting, updating, and deleting data from nested tables and varrays. Oracle stores data within a nested table and a varray differently. We will look at those differences.

Module Objectives:
When you have completed this module, you will be able to:
1. Insert, update, and delete records from nested tables
2. Insert, update, and delete elements from a varray
3. Explain the way Oracle stores data within nested tables and varrays

New Table Objects

Oracle8 and Oracle8i introduced a number of new table objects or types that DBAs have to worry about. Probably the most basic of these is the straight object table. But Oracle8 also introduced

nested tables,
varray types,
REF values, and
the entire concept of types.

Oracle has enhanced the concept of TYPE to more closely resemble the full object-oriented paradigm, adding limited inheritance and polymorphism. As a result, the Oracle RDBMS has become an object-relational database. This module addresses the Oracle8i object tables, the modifications and additions in Oracle, and the types used to build them.

Oracle PL/SQL Programming

Oracle Object Types

Before we can begin to discuss objects in Oracle, we have to address object types. As their name implies, an object type is used to define an object. To bridge the gap between the legacy versions Oracle7 and Oracle8, you can think of an object type as a predefined row that you can then use to build Oracle objects. Before you can build an object table in Oracle8, 8i or 9i, you must define its types. A table can consist of single columns, types, or a combination, as well as varrays. There are only object TYPEs. Under Oracle8i, the AUTHID clause was added to the CREATE TYPE command.
In Oracle, the CREATE TYPE command has been extended with the clauses required to support inheritance. In addition, Oracle type bodies support overloading, thus providing polymorphism. Let us look at a simple type definition and how it is used to build an Oracle object table. Suppose we want to define a real-world situation, such as a collection of pictures.

Question: What are the attributes of pictures?
How about 1) topic, 2) date and time taken, 3) photographer, 4) negative number, 5) picture number, and the on-disk location of the actual image? Let?s look at the type required to implement this structure:

CREATE TYPE 
   picture_t AUTHID CURRENT_USER 
AS OBJECT (
   negative#     number,
    Picture#      number,
   topic         varchar2(80),
    date_taken    date,
    photographer  person_t,
    picture    bfile);

What are the person_t and bfile columns?
Answer: The person_t is another type definition, "person type" which includes:

CREATE TYPE person_t AUTHID CURRENT_USER 
 AS OBJECT ( first_name  varchar2(32), 
          last_name    varchar2(32), 
          middle_init  varchar2(3), 
          gender          char(1), 
          address      address_t);

The embedded type address_t inside person_t is:

 CREATE TYPE address_t AUTHID CURRENT_USER 
 AS OBJECT (address_line1     varchar2(80),
         address_line2      varchar2(80), 
         street_name        varchar2(30), 
         street_number      number, 
         city               varchar2(30), 
         state_or_province  varchar2(2), 
         zip                number(5), 
         zip_4              number(4),  
        country_code       varchar2(20));

To make things more complex, METHODS can be declared in the type definition; then a type body must also be created. The BFILE definition was a new BLOB (binary large object) definition added in Oracle8 that specifies it as a LOB-stored external to the database. Anyway, back to the example: Now we want to create our picture object. This becomes:

CREATE TABLE pictures OF picture_t (
 CONSTRAINT pk_pictures PRIMARY KEY (negative#,picture#)
 USING INDEX TABLESPACE indexes);

When creating types, the order of creation is critical, unless we use incomplete types (covered in the next section). In the preceding example, the creation order must be: address_t, person_t, picture_t, and then the table pictures. You create from the most atomic-level type to the most inclusive. The AUTHID CURRENT_USER clause tells the kernel that any methods that may be used in the type specification should execute with the privilege of the executing user, not the owner. The default option for the AUTHID is DEFINER, which would correspond to the behavior in pre-Oracle8i releases, where the method would execute with the privileges of the user creating the type.
In the next lesson, we will describe the main concepts used when modifying nested tables and varrays.