Oracle was not the first RDBMS to introduce abstract data types (ADTs) for a relational database.
While Oracle was a significant early adopter and popularizer of ADTs, here's a timeline of earlier developments:
Early Pioneers:
Postgres (1986): Incorporated user-defined types (UDTs) in its initial release, paving the way for ADTs in other systems.
INGRES (early 1990s): Introduced a form of ADTs, allowing users to create custom data types with associated functions.
Oracle's Adoption:
Oracle Database 8 (1997): Introduced ADTs more prominently, expanding their capabilities and making them more accessible to developers.
Oracle's Influence: Played a key role in popularizing ADTs in the relational database world, demonstrating their potential for modeling complex data structures.
Other Systems:
IBM DB2 (1999): Added support for ADTs in its Version 7 release.
Microsoft SQL Server (2005): Introduced ADTs in its SQL Server 2005 version.
Key Points:
ADTs allow users to create custom data types tailored to specific application needs, enhancing data modeling capabilities.
They often include associated functions (methods) for data manipulation and behavior, extending the database's functionality.
By encapsulating data and behavior together, ADTs promote code reusability and maintainability.
Oracle's early adoption and significant contributions helped shape the evolution of ADTs in relational databases.
Limitations of Oracle 7
One of the shortcomings of the Oracle 7 database was the limited number of intrinsic data types.
A column in an Oracle 7 table could be CHAR, VARCHAR, INT, or NUMBER data types, but there was no way to extend the data types which existed in a table. Another limitation of Oracle 7 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:
Advantage to Grouping Values with Abastract Data Types
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.
An Abstract Data Type (ADT) consists of a data structure and subprograms that manipulate the data.
The variables that form the data structure are called attributes. The subprograms that manipulate the attributes are called methods.
ADTs are stored in the database and instances of ADTs can be stored in tables and used as PL/SQL variables.
ADTs let you reduce complexity by separating a large system into logical components, which you can reuse.
In the static data dictionary view *_OBJECTS, the OBJECT_TYPE of an ADT is TYPE.
In the static data dictionary view *_TYPES, the TYPECODE of an ADT is OBJECT.
CREATE TYPE Statement
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.
Notes:
If you create a type whose specification declares only attributes but no methods, then you need not specify a type body.
If you create a SQLJ object type, then you cannot specify a type body. The implementation of the type is specified as a Java class.
A standalone stored type that you create with the CREATE TYPE statement differs from a type that you define in a PL/SQL block or package.
With the CREATE TYPE statement, you can create nested table and VARRAY types, but not associative arrays. In a PL/SQL block or package, you can define all three collection types.
The next lesson shows how to imbed an OID in a table.
ADT - Exercise
Before you continue, click the Exercise link below to try creating an ADT. ADT - Exercise