• Contact
  • About Us

  • Home
  • ORACLE
  • UNIX
  • JAVA
  • IBM BPM
  • Interview Questions
You are here: Home / Oracle / Abstract Data Types in Oracle

Abstract Data Types in Oracle

January 27, 2014 by bhanu kishore Leave a Comment

Abstract Datatypes:

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
correlation.Column.Attribute

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

C.PERSON.ADDRESS.STREET

Related Posts

  • The better way of index selectivity
  • Mutation Error In Oracle database Triggers
  • Distinguish between user and schema in Oracle
  • Different Types of partitions in oracle with Examples.
  • what are the Analytic Functions in Oracle
Tweet
PinIt
Share it!
Share
on Facebook
Tweet
Share on Twitter
Share
on Google+

Filed Under: Oracle

About bhanu kishore

I am a IT professional working for MNC company Having more than 6 years of experience in Oracle and Unix and very much intrested in Oracle Applications. passionate about sharing knowledge with Others.
follow bhanu kishore on facebook ,twitter

Leave a Reply Cancel reply

You must be logged in to post a comment.

  • Facebook
  • Twitter

Recent Posts

  • LISTAGG In oracle With Example
  • regexpr_instr in oracle
  • Regexp_replace in oracle
  • Regexp_like in oracle
  • How to remove numeric values from a string in oracle

© Copyright 2014 123Techguru · Sitemap · Privacy Policy ·Contactus ·