What are user defined data types in Oracle?

In short, user defined types in Oracle are more complex data types based on the built-in (standard) data types and can be used in both PL/SQL and in SQL.

Let's examine this in more detail though.

In SQL the built-in data types fall into the following categories:
number data types (eg. NUMBER)
character data types (eg. VARCHAR2)
date and time data types (eg. DATE)
long and raw datatypes (eg. LONG)
large object data types (eg. CLOB)
and rowid data types (eg. ROWID)
and you can create columns in relational tables based on these types.

However since Oracle 8, the Oracle database has been able to support object-oriented data and this means you can create your own objects (as opposed to relations) based on object-oriented types which in turn are structures based on the built-in types.

Let's look at a few examples to show what we mean.

Let's suppose we wnat to store information about employees. In relational terms we create a table (a relation) based on the attributes (tuples) of an employee that we want to store. An employee table (relational) defintion therefore might look something like this:

CREATE TABLE employee
(employee_id NUMBER
,employee_name VARCHAR2(30)
,salary NUMBER
,dept_id NUMBER);

But, as just mentioned Oracle allows to define our own types, so we might define an employee type as follows:

CREATE TYPE employee_t AS OBJECT
(employee_id NUMBER
,employee_name VARCHAR2(30)
,salary NUMBER
,dept_id NUMBER);

We can then create a table of that type in the database:

CREATE TABLE employee OF employee_t;

Alternatively we can use that type for an attribute in a relational table in Oracle:


CREATE TABLE department
(emp employee_t
mgr varchar2(40));

Or as a nested table:

CREATE TYPE employee AS TABLE OF employee_t;

CREATE TABLE department
(dept_id NUMBER
,dept_name VARCHAR2(40)
,emps employee)
NESTED TABLE emps STORE AS dept_emps_tab;

The Oracle database also allows us to define types in terms of other user defined types. For example:

CREATE TYPE address AS OBJECT
(addr_line_1 VARCHAR2(240)
,addr_line_2 VARCHAR2(240)
,postal_code VARCHAR2(20));

CREATE TYPE person AS OBJECT
(name VARCHAR2(40)
,home_address address
,home_phone NUMBER
,work_address address
,work_phone NUMBER);

CREATE TABLE contacts
(contact_id NUMBER
,contact_details person);

User-defined types work much the same way in Oracle PL/SQL which has the same built-in data types as SQL plus a few extra ones such as BOOLEAN.

PL/SQL tables are known as collections and can be based on object types defined in SQL or types defined in PL/SQL. For example the following piece of code creates a type of collection known as an associative array:

DECLARE
TYPE address_ty IS TABLE OF VARCHAR2(120) INDEX BY VARCHAR2(10);
addresses address_ty;
BEGIN
addresses('home') := '1 The Close, Chiswick, SW3 4AB';
addresses('work') :=
'Bank of England, Threadneedle Street, London, EC2R 8AH';
END:

Asumming addr_ty has already been declared in our Oracle database as follows:

CREATE TYPE addr_ty IS TABLE OF VARCHAR2(20);

The following piece of PL/SQL code makes use of that type declaration.

DECLARE
addresses addr_ty;
BEGIN
addresses :=addr_ty('1 The Close, Chiswick, SW3 4AB', 'Bank of England, Threadneedle St., London, EC2R 8AH');
END;