PL/SQL object type

Learn how to create an object in pl-sql with example program.

A plsql object is a composite type that can be declared once and reuse many times where ever required.

Syntax

CREATE TYPE <object_type_name> AS OBJECT
(
<attribute_l> <datatype>,
<attribute_2> <datatype>
.
.
);
/

The above syntax shows the creation of ‘OBJECT’ with attributes.

attributes refers the column or field names in which data is stored.
attributes can be defined with any PL- SQL Data Type.

Example to create a PL-SQL object.

CREATE Or Replace TYPE AddressType AS OBJECT
 (
   street VARCHAR2(30),
   city   VARCHAR2(30),
   state  CHAR(30),
   zip    VARCHAR2(30)
 );

Note:
We can reuse this object types, whenever there will be a common fields requirement for multiple tables.

for Example,
for employee and student tables address type like street, city,state, pin-code are common.
Instead of creating address type fields in 2 tables,you can use the object type.

Example Program for OBJECT Type demonstration

Step-1: Execute below scripts for table creation

create table EMPLOYEE_TBL
(
  EMP_ID         VARCHAR2(4) not null,
  EMP_FIRST_NAME VARCHAR2(20),
  EMP_LAST_NAME  VARCHAR2(20),
  EMP_SALARY     NUMBER(8,2),
  EMP_CITY       VARCHAR2(10),
  ADDRESS        ADDRESSTYPE1
);


create table STUDENT_TBL
(
  STD_ID         VARCHAR2(4) not null,
  STD_FIRST_NAME VARCHAR2(20),
  STD_LAST_NAME  VARCHAR2(20),
  STD_CITY       VARCHAR2(20),
  ADDRESS        ADDRESSTYPE1
);

In the above table creation scripts, ADDRESSTYPE1 object was used instead of creating additional fields like street,city, zip code etc.
Here ADDRESSTYPE1 is object type which can be created once,and can used where whenever required.
This object type creation is a generic feature which will be used for multiple tables.

Step-2: Execute below scripts for data Insertion.

insert into Employee_TBL
  (EMP_ID, EMP_FIRST_NAME, EMP_LAST_NAME, EMP_SALARY, EMP_CITY, ADDRESS)
values
  ('1348',
   'VISWANATH',
   'ANNANGI',
   58000.00,
   'KADAPA',
   ADDRESSTYPE1('GACHIBOWLI', 'HYDERABAD', 'TELANGANA', '500032'));

insert into Employee_TBL
  (EMP_ID, EMP_FIRST_NAME, EMP_LAST_NAME, EMP_SALARY, EMP_CITY, ADDRESS)
values
  ('1349',
   'VINITH',
   'PAITHARI',
   27000.00,
   'WARANGAL',
   ADDRESSTYPE1('MADHAPUR', 'HYDERABAD', 'TELANGANA', '500081'));

insert into Employee_TBL
  (EMP_ID, EMP_FIRST_NAME, EMP_LAST_NAME, EMP_SALARY, EMP_CITY, ADDRESS)
values
  ('1350',
   'RAKESH ',
   'SINGH',
   158000.00,
   'LUCKNOW',
   ADDRESSTYPE1('HITECH CITY', 'HYDERABAD', 'TELANGANA', '500032'));

insert into STUDENT_TBL
  (STD_ID, STD_FIRST_NAME, STD_LAST_NAME, STD_CITY, ADDRESS)
values
  ('1348',
   'SANDIP',
   'ANNANGI',
   'KADAPA',
   ADDRESSTYPE1('GACHIBOWLI', 'HYDERABAD', 'TELANGANA', '500032'));

insert into STUDENT_TBL
  (STD_ID, STD_FIRST_NAME, STD_LAST_NAME, STD_CITY, ADDRESS)
values
  ('1349',
   'PARAMESH',
   'PAITHARI',
   'WARANGAL',
   ADDRESSTYPE1('MADHAPUR', 'HYDERABAD', 'TELANGANA', '500081'));

insert into STUDENT_TBL
  (STD_ID, STD_FIRST_NAME, STD_LAST_NAME, STD_CITY, ADDRESS)
values
  ('1350',
   'PRAKASH',
   'SINGH',
   'LUCKNOW',
   ADDRESSTYPE1('HITECH CITY', 'HYDERABAD', 'TELANGANA', '500032'));

object_type (field_names) is used To insert data for a object type.
For example
ADDRESSTYPE1(‘HITECH CITY’, ‘HYDERABAD’, ‘TELANGANA’, ‘500032’) are of object type .
Here ADDRESS is the object type. ‘HITECH CITY’, ‘HYDERABAD’, ‘TELANGANA’, ‘500032’ are the values for the fields in the object_type . STREET,CITY, STATE, ZIP are the object_type fields

Output

Select * from Employee_TBL;

Select * from STUDENT_TBL;