Pl-sql object table

Learn how to create an object type and use the object type to entire table without creating the additional columns.
Also Learn how to perform DML operations like insert, update, delete and select on object type.

below is the syntax to create an object.

CREATE OR REPLACE TYPE object_type_name AS OBJECT
 (      attribute1         data_type1
        attribute2         data_type,
       …
 );

Example query to create an object

CREATE OR REPLACE TYPE EMP_STD_TYPE AS OBJECT
(      id          NUMBER,
       name        VARCHAR2(15),
       description VARCHAR2(22),
       department  VARCHAR2(22),
       DOB   DATE
);

Example query to add additional attribute to object

alter type EMP_STD_TYPE 
add attribute (Gender varchar(10));

below is the syntax to create a table of object type

create TABLE tbl_name OF object_type_name;

Example query to create a table of object type

create TABLE EMP_TBL OF EMP_STD_TYPE;

Example queries to insert data into a object type table.

insert into EMP_TBL
values
  (EMP_STD_TYPE(1,
                'VISWANATH',
                'EMPLOYEE',
                'IT',
                TO_DATE('30/07/1991', 'DD/MM/YYYY'),
                TO_DATE('01/06/2019', 'DD/MM/YYYY'),
                'MALE'
               )
 );



insert into EMP_TBL
  (
   ID, 
   NAME,
   DESCRIPTION,
   DEPARTMENT,
   DOB,
   DOJ, 
   GENDER
)
VALUES
  (1,
   'VISWANATH',
   'EMPLOYEE',
   'IT',
    TO_DATE('30/07/1991', 'DD/MM/YYYY'),
    TO_DATE('01/06/2019', 'DD/MM/YYYY'),
   'MALE'
  );

Example query to select an individual column

SELECT e.id 
FROM EMP_TBL e;

Example query to select all columns

SELECT * 
FROM EMP_TBL e;

Example queries to update data into a object type table.

UPDATE EMP_TBL
   SET ID = 2
 WHERE ID = 1
 AND ROWNUM = 1;

Example queries to delete data from a object type table.

DELETE 
FROM EMP_TBL 
WHERE ID = 1;