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
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
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 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:
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';
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.
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