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.
Example: 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
to select Street which is availabe in ADDRESS_TY