One of the shortcomings of the Oracle7 database was the limited number of intrinsic data types.
A column in an Oracle7 table could be CHAR, VARCHAR, INT, or NUMBER data types, but there was no way to extend the data typing.
Another limitation of Oracle7 was that all entities had to be modeled at their smallest level.
For example, to select all of the address information from a table, we have to specify all of the columns in the group.
To illustrate, consider a customer table:
The option to group values is an advantage, because the many individual columns in an SQL query no longer have to be listed.
Creating Abstract Data Types (ADTs) also allows us to embed new constructs such as object ID (OIDs) and VARRAYs in table columns.
Embedded constructs have a huge impact on Oracle performance, since expensive JOINS can be avoided.
The next lesson shows how to imbed an OID in a table.
The CREATE TYPE statement creates or replaces the specification of one of these:
- Abstract Data Type (ADT) (including a SQLJ object type)
- Standalone stored varying array (varray) type
- Standalone stored nested table type
- Incomplete object type
An incomplete type is a type created by a forward type definition. It is called incomplete because it has a name but no attributes or methods.
It can be referenced by other types, allowing you define types that refer to each other. However, you must fully specify the type before you can use it to
create a table or an object column or a column of a nested table type.
The CREATE TYPE statement specifies the name of the type and its attributes, methods, and other properties.
The CREATE TYPE BODY statement contains the code for the
methods that implement the type.
Before you continue, click the Exercise link below to try creating an ADT.
ADT - Exercise