Abstract data types in the Oracle database

Preamble

​​

This article gives a brief overview of the important Oracle functionality that helps to facilitate the object-oriented programming process.

Central to object-oriented programming in Oracle are abstract types, also called object types.

Unlike conventional data types, abstract types contain not only the data structure but also the functions and procedures needed to manipulate them, combining data and behavior.

Object types are similar to other schema objects and consist of names, attributes, and methods. They resemble the concept of classes in C++ and Java. Oracle’s support for object-oriented functional tools, such as types, makes it possible to implement object-oriented mechanisms such as encapsulation and abstraction when modeling complex objects and processes from real life. In addition, Oracle supports single inheritance of user-defined SQL types.

CREATE TYPE command

Object types are created by users and stored in a database like other Oracle data types, for example, VARCHAR2. The CREATE TYPE command allows you to create an abstract template corresponding to the real-world object. Below is an example of how to use this command:

SQL> CREATE TYPE person AS object
2 (name varchar2(30),
3 phone varchar2(20));
Type created.
SQL>

Object Table

Object tables contain objects like the personality type, which was created in the previous section. Below is an example of how to create an object table:

SQL> CREATE TABLE person_table OF person;
Table created.
SQL>

The interesting part is this. There are no unambiguous (single-value) columns in object tables like in conventional Oracle tables: all columns are types and can therefore store multiple values. Object tables can be used to view data both as a single-column table and as a table with multiple columns consisting of object type components. The example below shows how you can insert data into an object table:

SQL> INSERT INTO person_table
2 VALUES
3 ('john smith', '1-800-555-9999');
1 row created.
SQL>

Collections

Collections are ideal for representing one-to-many relationships between data. Oracle supports two basic types of collections: VARRAY arrays and nested tables. These are all discussed in more detail in the next two sections of my article.

VARRAY arrays

The VARRAY array is an ordered collection of data. Each element in this array has a specific index, which is used to access it. Below you can declare the VARRAY type:

SQL> CREATE TYPE prices AS VARRAY (10) OF NUMBER (12,2);

Attached tables

The nested table is an ordered set of data elements. This ordered set can refer to an object type as well as to any of the built-in Oracle types. A simple example is given below:

SQL> CREATE TYPE lineitem_table AS TABLE OF lineitem;

You can use the TABLE operator to access collection items using SQL code, as shown in the following example. Here, it is a nested table and a column in which data is to be inserted:

SQL> INSERT INTO
TABLE(SELECT courses FROM department WHERE name = 'History')
VALUES('Modern India');

Inheritance of types

Not only types can be created, but also type hierarchies consisting of parent supertypes and child subtypes related to the parent inheritance. Here is an example of how to create a subtype from a supertype. First, a supertype is created:

SQL> CREATE TYPE person_t AS OBJECT (
name varchar2(80),
social_sec_no number,
hire_date date,
member function age() RETURN number,
member function print() RETURN varchar2) NOT FINAL;

Then a subtype is created which will inherit all attributes and methods from the given supertype:

SQL> CREATE TYPE employee_t UNDER person_t
(salary number,
commission number,
member function wages () RETURN number,
OVERRIDING member function print () RETURN varchar2);

Oracle database

The CAST operation allows you to do two things: to convert the built-in data types, and to convert the collection type value into another collection type value.

Below is an example of how to apply CAST operation with built-in data types:

SQL> SELECT product_id,
CAST(description AS VARCHAR2(30))
FROM product_desc;

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.