Dear readers of our blog, we'd like to recommend you to visit the main page of our website, where you can learn about our product SQLS*Plus and its advantages.
 
SQLS*Plus - best SQL Server command line reporting and automation tool! SQLS*Plus is several orders of magnitude better than SQL Server sqlcmd and osql command line tools.
 

REQUEST COMPLIMENTARY SQLS*PLUS LICENCE

Enteros UpBeat offers a patented database performance management SaaS platform. It proactively identifies root causes of complex revenue-impacting database performance issues across a growing number of RDBMS, NoSQL, and deep/machine learning database platforms. We support Oracle, SQL Server, IBM DB2, MongoDB, Casandra, MySQL, Amazon Aurora, and other database systems.

Abstract data types in the Oracle database

10 September 2020

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;

 
Tags: , , ,

MORE NEWS

 

Preamble​​NoSql is not a replacement for SQL databases but is a valid alternative for many situations where standard SQL is not the best approach for...

Preamble​​MongoDB 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...

Preamble​​MS 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...

Preamble​​Atom 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...

Preamble​​MongoDB 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...

Preamble​​SQLShell 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...

Preamble​​Writing 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...

Preamble​​Oracle Coherence is a distributed cache that is functionally comparable with Memcached. In addition to the basic function of the API cache, it...

Preamble​​IBM 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....

Preamble​​If 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...

Preamble​​Starting with Microsoft SQL Server 2008, by default, the group of local administrators is no longer added to SQL Server administrators during the...