User defined Types in Oracle

External datatypes specify how data is stored in host variables.

When your host program inputs data to Oracle, if necessary, Oracle converts between the external datatype of the input host variable and the internal datatype of the target database column.

When Oracle outputs data to your host program, if necessary, Oracle converts between the internal datatype of the source database column and the external datatype of the output host variable.

A new category introduced is User-Defined Types, which are very useful in situations where the same data type is used in several columns from different tables; this data type might change in the future and so this mechanism assures that it will remain consistent.

There is also added legibility for User-defined types because of the extra information that the names of the user-defined types provides.

Not only should the data type remain the same; the nullability, default value and check constraints or rules should be identical.

In the case of rules, one rule would be bound to the User-defined type and that would be enough.

With check constraints, the best solution would be to replace the constraints for the tables by using ALTER TABLE statements.

The main data types which cause constraints in oracle are:

BIT type is not supported. There is a BOOLEAN type in PL/SQL but it cannot be used for a database column.

A bit type variable may be either true or false. In oracle, we need the idea of three-valued logic. A column can be true, false, or NULL. (More on NULLs here).

When building the data model you must affirmatively decide whether a NULL value will be permitted for a column and, if so, what it means.

Domains, type aliases are not supported.

Dates and times are supported differently in Oracle.

CHAR(n) can be of length up to 2000.
VARCHAR(n) can be of length up to 4000.

However, special treatment is required when bulk-loading strings longer than 255 characters.

The LONG, RAW, and LONG RAW data types have many limitations so data stored in these data types can be hard to work with.

Another issue is chaining of rows in a table. In Oracle, data is stored in logical units called blocks.

Block size is chosen when a database is created, and is typically 2K, 4K, 8K, or 16K.

When you store a row of data in a table and the entire row won’t fit inside one block, then part of the row is stored in one block and a pointer links the block to another block where more of the row is stored.

This is called a chained row. In Oracle long pieces of unstructured data can lead to many chained rows in the database.

Oracle databases can become unstable in situations where huge numbers of chained rows exist.

If you choose to store "unstructured" data in Oracle using the LONG or LONG RAW data types, store the unstructured data in a separate table used just for that purpose.

The table should hold only the "unstructured" data itself and a foreign key to link the data to structured data. This foreign key should also be the primary key for the table.

Use Oracle’s declared integrity constraints in order to ensure integrity between the structured and unstructured data. This strategy can improve performance when accessing the structured data (by reducing chaining and interleaving with "unstructured" data) and will confine any potential database instability to the unstructured data only.

Another new concept in User-defined types and constraints is “Types” which are physical structures that serve as templates or building blocks for other data types and/or tables.

Whereas Oracle Designer uses the term Domains, ODD uses Value Types, and the Oracle Server simply calls them Types.

Single column, user-defined Data Types are referenced by their name, eliminating the need to provide an intuitive name to the member placeholders within the Data Types.

There are several kinds of user-defined types (UDTs). Some for each type and its constraints are shown below:

1. Column types: These have no multiple column types but are valid only for single column. The code for a single column is shown below:

   CREATE OR REPLACE TYPE NAME_T AS OBJECT (
   COL VARCHAR2 (30))

   But the problem with column type is that the basic datatype is only valid for a column and if chained records are stored in more than one column then this type cannot be used.

2. Multi-Column:

   CREATE OR REPLACE TYPE ADDR_T AS OBJECT (
   ADDR1 VARCHAR2 (50),
   ADDR2 VARCHAR2 (50),
   CITY VARCHAR2 (30),
   STATE VARCHAR2 (2),
   ZIP_4 VARCHAR2(9));

   Here each column can have different type but it is not applicable to individual fields.

3. Row Types: These include single and multiple rows and form the foundation of object tables/views:

   CREATE OR REPLACE TYPE EMP_T AS OBJECT (
   EMP_ID NUMBER (10),
   LNAME_TX NAME_T,
   FNAME_TX NAME_T,
   BIRTH_DATE DATE);

   Not supported by query systems.

4. Default values such as:

   BOOLEAN_YN := ‘Y’
   BOOLEAN_NY := ‘N’ are not supported.

5. Constraints such as "AGE BETWEEN 18 AND 65" are hard to implement and check.
6. Flag / Indicator types are mandatory.

Return to Oracle Database constraints