How to Create Object Type In SQL

Let's understand benefit of Object type before creating .

We may require to hold particular data in Single column,example you may want store address in one column but address will have mutiple fields ,say H.no,Street,Area,City,Pin code .

So how are you going to store in Single column all above data . In this kind of scenario's only we have to think about OBJECT TYPE in SQL.

Object Type will hold all above details in single column as we required, It means object type will work as DATA TYPE in SQL.

Let's see how can we achieve this.

1st Create object type in whichever way you required.

Syntax :

Create Or Replace Type Type_Name Is Object
(Col1 Datatype,Col2 DataType....... Coln DataTypen);


Here "Or Replace" ill replace object type if its already exist in your schema.

Eg:

SQL> create or replace type address_Object is object
  2  (H_no Varchar2(100),
  3  Street Varchar2(100),
  4  Area Varchar2(100),
  5  City Varchar2(100),
  6  Pincode Integer);
  7  /

Type created


Create table using above Object type.

here I'm using "address_Object" object type as Data type for one column

SQL> CREATE TABLE CUSTOMER (C_ID INTEGER,NAME VARCHAR2(100),CUST_ADDRESS address_Object);

Table created

While Inserting/Storing data for  CUST_ADDRESS, you need to observe the Syntax here.

SQL> INSERT INTO CUSTOMER VALUES (101,'VENKY',address_Object(H_no => '102/12',Street => 'Kaveri',Area => 'HSR layout',City => 'Bangalore',Pincode => '560102'));

1 row inserted
 
SQL> INSERT INTO CUSTOMER VALUES (102,'Teja',address_Object(H_no => '103/12',Street => 'Meenakshi',Area => 'Marathahalli',City => 'Bangalore',Pincode => '560037'));

1 row inserted
 
SQL>  


While Querying Customer table ,You can see how data fetching and showing .

Data will show  Main column with Dot(.) and respective fields which are created under Object Type.

To fetch any particular column from this object type table you need to use alias for this table and also need to mention Column_name and field name as below.

 

There may be situation where we need to do Update so how to do DML operation on this Object Type Table.

 As you have used above, like table alias dot(.) and main column Name dot(.) and Field name which you want to update.

Eg:


SQL> Update CUSTOMER c
  2  Set  c.CUST_ADDRESS.City='Hyderabad'
  3  Where C_id=101
  4  /

1 row updated

Pls post your queries if you have any doubt on this.Thanks.