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.
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.
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 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:
picture_t AUTHID CURRENT_USER
AS OBJECT (
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),
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:
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.