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 Oracle PL/SQL and in SQL.
Let's examine this in more detail.
In Oracle 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 want 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 definition might look something like this:
CREATE TABLE employee
But, as just mentioned Oracle allows us to define our own types, so we might define an employee type as follows:
CREATE TYPE employee_t AS OBJECT
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
Or as a nested table:
CREATE TYPE employee AS TABLE OF employee_t;
CREATE TABLE department
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
CREATE TYPE person AS OBJECT
CREATE TABLE contacts
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 also 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 an associative array:
TYPE address_ty IS TABLE OF VARCHAR2(120) INDEX BY VARCHAR2(10);
addresses('home') := '1 The Close, Chiswick, SW3 4AB';
'Bank of England, Threadneedle Street, London, EC2R 8AH';
These are also known as index-by tables.
Assuming we have a type addr_ty already 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.
addresses :=addr_ty('1 The Close, Chiswick, SW3 4AB', 'Bank of England, Threadneedle St., London, EC2R 8AH');
That just briefly describes the use of user-defined types in Oracle PL/SQL. For more information see our PL/SQL tutorials
and in particular the tutorials on defining and using PL/SQL collections
Also see our Oracle training
page for information about formal training courses both on-line and in person.