REQUEST COMPLIMENTARY SQLS*PLUS LICENCE
Abstract data types in the Oracle database
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);
Operation CAST
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;
MORE NEWS
PreambleNoSql is not a replacement for SQL databases but is a valid alternative for many situations where standard SQL is not the best approach for...
PreambleMongoDB Conditional operators specify a condition to which the value of the document field shall correspond.Comparison Query Operators $eq...
5 Database management trends impacting database administrationIn the realm of database management systems, moreover half (52%) of your competitors feel...
The data type is defined as the type of data that any column or variable can store in MS SQL Server. What is the data type? When you create any table or...
PreambleMS SQL Server is a client-server architecture. MS SQL Server process starts with the client application sending a query.SQL Server accepts,...
First the basics: what is the master/slave?One database server (“master”) responds and can do anything. A lot of other database servers store copies of all...
PreambleAtom Hopper (based on Apache Abdera) for those who may not know is an open-source project sponsored by Rackspace. Today we will figure out how to...
PreambleMongoDB recently introduced its new aggregation structure. This structure provides a simpler solution for calculating aggregated values rather...
FlexibilityOne of the most advertised features of MongoDB is its flexibility. Flexibility, however, is a double-edged sword. More flexibility means more...
PreambleSQLShell is a cross-platform command-line tool for SQL, similar to psql for PostgreSQL or MySQL command-line tool for MySQL.Why use it?If you...
PreambleWriting an application on top of the framework on top of the driver on top of the database is a bit like a game on the phone: you say “insert...
PreambleOracle Coherence is a distributed cache that is functionally comparable with Memcached. In addition to the basic function of the API cache, it...
PreambleIBM pureXML, a proprietary XML database built on a relational mechanism (designed for puns) that offers both relational ( SQL / XML ) and...
What is PostgreSQL array? In PostgreSQL we can define a column as an array of valid data types. The data type can be built-in, custom or enumerated....
PreambleIf you are a Linux sysadmin or developer, there comes a time when you need to manage an Oracle database that can work in your environment.In this...
PreambleStarting with Microsoft SQL Server 2008, by default, the group of local administrators is no longer added to SQL Server administrators during the...