Abstract Datatypes in Oracle

   Abstract Datatypes are datatypes that consist of one or more subtypes. Rather than being constrained to the standard Oracle datatypes of NUMBER, DATA, and VARCHAR2, abstract datatypes can more accurately describe your data.

Ex:-
     create type ADDRESS_TY as object
      (Street  VARCHAR2(20),
       City    VARCHAR2(10),
       State   CHAR(10),
       Pin     NUMBER);

     Now the datatype ADDRESS_TY is been created. You can use this datatype alongwith other datatypes. For ex. will create a standard datatype for people which contain Name and address of a person.

   create type PERSON_TY as object
    ( Name VARCHAR2(20),
      Address ADDRESS_TY);

Now PERSON_TY contains Name and address of a person  we can use this to create table.

    You can't insert data into PERSON_TY. The reason is straightforward: A datatype describes data, it does not store data. You cannot store data in a NUMBER datatype, and you cannot store data in a datatype that you define, either. To store data, you have to create a table that uses your datatype.

The following command create a table name CUSTOMER.

  create table CUSTOMER
  (Customer_ID NUMBER,
   Person    PERSON_TY);
 
 To Insert rows into CUSTOMER do following

  insert into CUSTOMER VALUES
   (1, PERSON_TY('HARI',ADDRESS_TY('#102 Lokhand wala','mumbai','MH',10101);
 
to select data from CUSTOMER table

    Select customer_ID, c.person.name
     from CUSTOMER C;

  First, Note that the access of the datatype's attribute requires the use of a table alias. A table alias, also known as a correlation variable, allows Oracle to resolve any ambiguity regarding the name of the object being selected. As a column name, Person.Name points to the Name attribute within the PERSON_TY datatype. The format for the column name is
     correlation.Column.Attribute

   to select Street which is availabe in ADDRESS_TY
 correlation.Column.column.Attribute

C.PERSON.ADDRESS.STREET