Oracle Data Types - User-defined Types

varrays
and nested tables

Object types

An Oracle database is an object-relational hybrid which means that it supports both objects and the traditional relational database features - tables and columns.

By providing support for objects in the database Oracle eliminates the need for object-oriented applications to translate their object data models into relational models as well as the need at run-time to map object access requests to relational table access.

Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber to Oracle Tips and Tricks, you're missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer. Subscribe now and ignite your career.

An Oracle data type that is an object has 3 parts:

a name (which must be unique within the schema)
attributes (which define the object's properties and may be of built-in or other user-defined types)
and methods which are procedures or functions written in PL/SQL that implement operations that the object can perform.

New object types are created using the CREATE OR REPLACE TYPE statement in SQL. This defines the name of the object and its attributes. Methods are defined as part of the CREATE TYPE BODY statement;

For example to create new type called PERSON you might define it like this:

CREATE OR REPLACE TYPE person IS OBJECT
(first_name VARCHAR2(30);
last_name VARCHAR2(30);
address address_type,
MEMBER FUNCTION name RETURN VARCHAR2);

Where address_type is another user-defined type and the function name is a method the detail of which is defined by the CREATE TYPE BODY statement like this:

CREATE TYPE BODY person IS
MEMBER FUNCTION name RETURN VARCHAR2 IS
BEGIN
RETURN first_name||' '||last_name;
END;
END;

The methods that can be defined for object Oracle data types include:

a CONSTRUCTOR function which is used to instantiate an object of that type at run-time
a MAP function which returns the relative position of a given object instance in the ordering of all instances of the object (i.e. this person is #3)
an ORDER function which returns a value of -1, 0 or 1 to indicate that the object instance is less than, equal to, or greater than another specified object instance
STATIC methods (these cannot reference implicitly the object with which they are associated)
MEMBER methods (these can reference implicitly the object with which they are associated)

Note that an object type can have either a MAP function or an ORDER function, not both.

It is also worth noting that object types can be used in relational tables as well as object tables.

For example, we could use the Oracle data type Person that we've just defined in a relational table declaration like this:

CREATE TABLE customers (cust_id Number(12), cust person);

To create an object table using this type the declaration would be like this:

CREATE TABLE customers OF person;

In this case, each row of the table contains an instance of type Person. The instance is automatically assigned a unique, system-generated object identifier when a row is inserted.

Nested Tables

Nested table Oracle data types are useful where you have a master-detail/parent-child relationship between 2 entities and where the number of detail records is unknown. A good example of this is invoices and invoice lines.

For example, to create, an invoice table with a nested invoice-line table, first define the object type for the invoice details:

CREATE OR REPLACE TYPE inv_dtl AS OBJECT
(
     Inv_line_id NUMBER(5),
     Item_desc   VARCHAR2(120),
     Item_Amt NUMBER(6,2)
);

Next, define the table type based on the object type:

CREATE OR REPLACE TYPE inv_dtl_tab AS TABLE OF inv_dtl;

Finally define the parent table with the nested table:

CREATE TABLE invoice(
     Invoice_id NUMBER(5),
     Cust_id NUMBER(6),
     Total NUMBER(9,2),
     Lines inv_dtl_tab
)
NESTED TABLE lines STORE AS invoice_lines;

The STORE AS clause defines the name of the segment used to hold the nested table and is necessary because nested tables are stored in a different segment to (but in the same tablespace as) the main table.

To access a nested table, you have to un-nest the table:

SELECT invoice_id, inv_line_id, item_desc, item_amt
FROM invoice i, TABLE(i.lines)
WHERE invoice_id = 1;

VARRAYS

A VARRAY is an Oracle data type that is useful when the maximum number of detail records is small and is known in advance (such as a list of phone numbers for a customer e.g. office, home and mobile). Note that there is no storage clause for the varray, because they are stored in line.

CREATE TYPE phone_no_type AS OBJECT
        ( Desc     VARCHAR2(20)
         ,Phone_no VARCHAR2(20)
        );

CREATE TYPE phone_no_list AS VARRAY(3) OF phone_no_type;

CREATE OR REPLACE TYPE cust
          AS OBJECT (
              Cust_id   NUMBER(12)
             ,Name      VARCHAR2(75)
             ,Phone_nos phone_no_list)
          );

The elements of a varray cannot be accessed individually using SQL, but they can be accessed in PL/SQL and 3GLs.

REF Data Types

The REF Oracle data type is used to establish a relationship from one object to another by storing the object identifier (the object's unique identifier) of another object. This relationship is defined by means of a reference in the object table definition. This acts like a foreign key, linking one object to another object in the database (via the unique object identifier – the object’s primary key). Oracle uses the link automatically to perform the same function as a join between relational tables.

For example consider a new object called inv which in a relational database would have a foreign key relationship to the customer table. In object terms this becomes a new object referencing an existing object as follows:

CREATE OR REPLACE TYPE inv AS OBJECT
(
Invoice_id NUMBER(12)
,Client REF cust
,Total NUMBER(9,2));

CREATE TABLE invoice OF inv (id PRIMARY KEY);

When a REF value points to a nonexistent object, the REF is said to be "dangling" (not the same as a null REF). The condition IS [NOT] DANGLING is used to determine whether or not a REF is dangling.

For example, using the object Oracle data type CUST as just defined and the table INVOICE the following statement would retrieve all of the names of customers who have a record in the table.

SELECT i.client.name
FROM invoice i
WHERE i.client IS NOT DANGLING;

User-Defined Oracle Data Types

User-defined types

Object types

Oracle Tips & Tricks to SKYROCKET Your Career!

Nested Tables

VARRAYS

REF Data Types

Looking for Oracle training?