BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








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.


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


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;
Gallery_id number,
Owner person_t,
Gallery_address address_t,
Contents picture_nt);
CREATE TABLE galleries OF gallery_t (
PRIMARY KEY (Gallery_id)
STORAGE (INITIAL 100k NEXT 100k pctincrease 0))
NESTED TABLE contents STORE AS galleries_ntab (

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)
STORAGE (INITIAL 100k NEXT 100k pctincrease 0))

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 (

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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter