User defined Types in OracleBackground: Oracle recognizes two kinds of datatypes which are internal and external. Internal datatypes specify how Oracle stores data in database columns.
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.
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:
Free eBookSubscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:
What visitors say...
"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.Read more Testimonials
I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.
Return to top
The name Oracle is a trademark of Oracle Corporation.