Remote DBA Plans
Remote DBA Service

The Incomplete Type

Oracle Tips by Burleson Consulting

The command to create an incomplete type is shown next. Incomplete types are required for circular reference situations. Incomplete types specify no attributes.

CREATE OR REPLACE TYPE type_name;

An incomplete type would be used where a type references a second type that references the first type (a circular reference such as emp-supervisor). This allows the incomplete type to be referenced before it is completed. However, before a table can be constructed from an incomplete type, it must be completed.

The Varray Type

Varrays are used for small sets of related items. For example, a house has several rooms, and each room has measurements. Let’s assume that a house of a certain square footage can’t have more than 10 rooms. Thus, a varray to hold this room data would be sized at a limit of 10, so the command to create a varray type would be:

CREATE OR REPLACE TYPE [schema.]type_name AS|IS

VARRAY|[VARYING ARRAY] (LIMIT) OF datatype;

Note: Datatype cannot be of type rowid, LONG, or LONG RAW; it can be a built-in datatype, a REF, or an object-type (excluding LOB datatypes such as BLOB, CLOB, NCLOB, or BFILE). In releases earlier than Oracle9i, a varray cannot contain a nested table type or a varray as a direct element; in 9i, this is allowed. New in Oracle8i was the ability to store varrays out-of-line in LOB storage-specified areas.

A varray should be used when the number of items to be stored in the type is:

* Known and fixed.

* Small (this is a relative term; remember, the data is stuffed into a RAW and stored inline with the rest of the type’s data unless a special LOB storage area is specified for the varray under Oracle8i).

Since release 8i, a varray can be used in a partitioned table. In earlier releases (up to and including 8.0.3), varrays took up an inordinate amount of space. Since Oracle8i, you can store them out-of-line in a specified LOB storage area.

Varrays cannot be sparse, they must be contiguous from the first entry to the last item entered; no “holes” are allowed. An example would be when a person or company has multiple addresses. In this case, we could use the address_t type to make a varray since usually the number of addresses will be small (say, a mailing address, a work address, and a home address, for a total of three):

CREATE OR REPLACE TYPE address_v AS VARRAY (3) OF address_t;

The Nested Table Type

A nested table is used where the same data is repeated for a given entity an unknown number of times, such as the one-to-many relationships between a parent and children. In applications where storage is at a premium, a nested table may actually be more efficient (at least in early 8.0 releases) than a varray. If you have the time, check both types of objects for storage usage before committing your design to one or the other. Another positive side of nested tables is that they can be sparse.

The command to create a NESTED TABLE type would be:

CREATE OR REPLACE TYPE [schema.]type_name IS|AS TABLE OF datatype;

A NESTED TABLE should be used when:

* The number of items is large or unknown.

* The storage of the items needs to be managed.

A NESTED TABLE is stored in a STORE TABLE, which must be specified in the CREATE TABLE command for each NESTED TABLE type used. The NESTED TABLE type can be used in partitioned tables after release 8i. Some early-release documentation states that Oracle itself specifies the store table name; this is incorrect. Nested tables cannot contain NCLOBs, varrays, or other nested tables. A nested table can contain a BLOB, BFILE, or CLOB datatype. An example would be if we wanted to embed a nested table of pictures into a gallery type:

CREATE TYPE picture_nt AS TABLE OF picture_t;
/
CREATE TYPE gallery_t AS OBJECT (
Gallery_id number,
Owner person_t,
Gallery_address address_t,
Contents picture_nt);
/
CREATE TABLE galleries OF gallery_t (
PRIMARY KEY (Gallery_id)
USING INDEX TABLESPACE graphics_index
STORAGE (INITIAL 100k NEXT 100k pctincrease 0))
OBJECT ID PRIMARY KEY
NESTED TABLE contents STORE AS galleries_ntab (
(PRIMARY KEY (NESTED_TABLE_ID, picture#))
ORGANIZATION INDEX COMPRESS)
RETURN AS LOCATOR
TABLESPACE tools
STORAGE (INITIAL 2m NEXT 2m PCTINCREASE 0);

Let’s look more closely at the CREATE TABLE command as shown above and see what exactly we are telling Oracle to do:

CREATE TABLE galleries OF gallery_t (

In this statement, we are instructing Oracle to use the defined type gallery_t to create a table using the specified type to define the columns in the table.

PRIMARY KEY (Gallery_id)
USING INDEX TABLESPACE graphics_index
STORAGE (INITIAL 100k NEXT 100k pctincrease 0))
OBJECT ID PRIMARY KEY

In this next statement, we are telling Oracle to make gallery_id the primary key for galleries, as specified in the gallery_t definition. We are placing the index in the graphics_index tablespace with the specified storage parameters. The OBJECT ID PRIMARY KEY clause tells Oracle that the primary key value is to be used as the row’s OID.

NESTED TABLE contents STORE AS galleries_ntab (
(PRIMARY KEY (NESTED_TABLE_ID, picture#))
ORGANIZATION INDEX COMPRESS)
RETURN AS LOCATOR

This section of the command tells Oracle to store the nested table type values in the nested table named galleries_ntab. This will be a separate table from the galleries table. Note that you cannot specify the location for storing the nested table. If the table is nonpartitioned, it defaults to the same location as the table itself; if the table containing the nested table is partitioned, then the location for the nested table storage defaults to the default tablespace for the schema (user) in which it is created.

The NESTED_TABLE_ID is a special column included in all nested tables; it is used to identify the row in the source table to which the values in the nested table relate. By tying the NESTED_TABLE_ID and the picture# together, we ensure uniqueness in the nested table.

The ORGANIZATION INDEX COMPRESS clause forces the nested table into an IOT configuration, saving space. The COMPRESS keyword suppresses the storage of the NESTED_TABLE_ID in each row, saving more space.

Finally, the RETURN AS LOCATOR clause returns a locator value that can then be decoded with the Oracle Call Interface call, OCICollIsLocator, or a call to the stored package procedure, UTL_COLL.IS_LOCATOR, to return the details of the rows in the nested table. This is important because if you don’t specify the RETURN AS LOCATOR, the default is to return VALUE, which returns all values in the nested table that corresponds to your request. If the gallery we were probing was the Metropolitan Museum of Art in New York City, the return values could number in the tens of thousands.

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.