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.
Before we can begin to discuss objects in Oracle8 and Oracle8i,
we have to address object types.
As their name implies, an object type is used to define an object. To bridge the gulf between Oracle7 and Oracle8,
you can think of an object type as a predefined row that you can then use to build Oracle objects.
When you have completed this module, you will be able to:
Insert, update, and delete records from nested tables
Insert, update, and delete elements from a varray
Explain the way Oracle stores data within nested tables and varrays
In the next lesson, we will describe the main concepts used when modifying nested tables and varrays.
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 (which are discussed later in this chapter).
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.
Also in 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 topic, date and time taken, photographer, negative number, picture number, and the on-disk location of the actual image? Let?s look at the type required to implement this structure:
picture_t AUTHID CURRENT_USER
AS OBJECT (
Notice something odd? What are the person_t and bfile columns? The person_t is another type definition, ?person type,? which includes:
CREATE TYPE person_t AUTHID CURRENT_USER
AS OBJECT ( first_name varchar2(32),
The embedded type address_t inside person_t is:
CREATE TYPE address_t AUTHID CURRENT_USER
AS OBJECT (address_line1 varchar2(80),
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:
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.
CREATE TABLE pictures OF picture_t (
CONSTRAINT pk_pictures PRIMARY KEY (negative#,picture#)
USING INDEX TABLESPACE indexes);
The AUTHID CURRENT_USER clause tells the kernel that any methods that may be used in the type specification (in the above example, none)
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.