Abstract Datatype

Abstract datatype are datatypes that consists of one or more subtypes. This datatype is used to describe the data accurately. Abstract datatypes can be nested and can contain references to other abstract datatype. This datatype can be reused. Abstract datatype is object oriented thing.

Creating abstract datatype

CREATE TYPE STUDENT_NAME_TY AS OBJECT

(FIRST_NAME VARCHAR2(15), LAST_NAME VARCHAR2(15));

In above example AS OBJECT identify STUDENT_NAME_TY as an object implementation. 

Using abstract datatype in table

CREATE TABLE STUDENT

(STUDENT_ID NUMBER(4) PRIMARY KEY,

NAME STUDENT_NAME_TY);

Viewing details of table

DESC STUDENT;

or we can use the following

SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS

WHERE TABLE_NAME='STUDENT';

or we can use the following

SELECT ATTR_NAME, LENGTH, ATTR_TYPE_NAME FROM USER_TYPE_ATTRS

WHERE TYPE_NAME='STUDENT_NAME_TY';

Inserting data in table

INSERT INTO STUDENT VALUES 

(1,STUDENT_NAME_TY('Abhimanyu','Kumar'));

or we can use the following

INSERT INTO STUDENT VALUES

(&STUDENT_ID, STUDENT_NAME_TY('&FIRST_NAME, &LAST_NAME'));

Selecting data from table

SELECT STUDENT_ID, S.NAME.FIRST_NAME FROM STUDENT S;