Lesson 3	Abstract data types
Objective	Create an abstract data type.

Oracle Abstract Data Types

Abstract Data Types in Oracle

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:

Consider the customer table above. Many individual columns in an SQL query are listed
In Oracle, these data columns can be grouped together by data type
and the data types name_type and full_address_type are created.
Now we can create the table using the new ADT's that we have created.
In the code above, we have used the name_type and full_addrress type just as if they were an intrinsic Oracle data type such as CHAR or INT.
Now that the Oracle table is defined, we can reference cust_address in our SQL just as if it were a primitive data type

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.

Embedded data constructs and SQL joins

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.

Abstract Data Types

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.

ADT - Exercise

Before you continue, click the Exercise link below to try creating an ADT.
ADT - Exercise

Oracle DBA Cloud