Oracle Object Types

Object Types are user-defined data types in Oracle.
These types can be created from
1.Built-in datatypes (NUMBER,VARCHAR,DATE etc)
   or
2.Previously defined object types,object references and collection types.

Let see a use case where address (Street,City,PINCODE,Country) is needed for both EMPL and CUSTR tables . So instead of defining all these attributes in both tables we can create one TYPE for address and this TYPE can be leveraged in both tables as user-defined datatype for address column.

CREATE TYPE ADDR_TYPE AS OBJECT
(
STREET_NO VARCHAR2(20),
CITY VARCHAR2(20),
PINCODE NUMBER(6),
COUNTRY VARCHAR2(20)
) ;

CREATE TABLE EMPL
(
EMP_ID NUMBER,
EMP_NAME VARCHAR2(50),
SALARY NUMBER,
EMP_ADDR ADDR_TYPE --- ADDR_TYPE is Object Type
);

CREATE TABLE CUSTR
(
CUST_ID NUMBER,
CUST_NAME VARCHAR2(50),
CUST_CONTACT VARCHAR2(20),
CUST_ADDR ADDR_TYPE  --- ADDR_TYPE is Object Type
)

Let see how we can play around with these object types

1.INSERT data

INSERT INTO EMPL (1321,'Aravind',2789,ADDR_TYPE('17-1-383','Hyderabad',500059,'India'));

2.UPDATE data

UPDATE EMPL e set e.EMP_ADDR.STREET_NO='17-1-383/190') where  EMP_ID=1321 ;

3.SELECT data

SELECT e.EMP_ID,e.EMP_NAME,e.EMP_ADDR.STREET_NO,e.EMP_ADDR.CITY from EMPL e;

MEMBER FUNCTION within Object types.

CREATE TYPE MARKS_TYPE as OBJECT
(
SUB1 NUMBER,
SUB2 NUMBER,
MEMBER FUNCTION TOT_MARKS RETURN NUMBER,
MEMBER FUNCTION RESULT RESULT VARCHAR2
);

We have to define member functions code in object type body.

CREATE TYPE BODY MARKS_TYPE AS
(
MEMBER FUNCTION TOT_MARKS RETURN NUMBER IS
BEGIN
RETURN(SUB1+SUB2);
END;

MEMBER FUNCTION RESULT RETURN VARCHAR2 IS
BEGIN
IF SUB1<35 OR SUB2<35
THEN RETURN('FAIL');
ELSE RETURN('PASS');
END IF;
END;
);

Examples

CREATE TABLE STUDENT
(
H_T_NO NUMBER,
NAME VARCHAR2(50),
MARKS MARKS_TYPE
);

INSERT INTO STUDENT (0606256,'Aravind',MARKS_TYPE(52,91));

SELECT S.H_T_NO,S.NAME,S.MARKS.SUB1,S.MARKS.SUB2,S.MARKS.TOT_MARKS(),S.MARKS.RESULT
FROM STUDENT S;