Creating and Using Type in Oracle Database
Oracle Database allows developers to create their own datatypes and use them in column of database tables. These user-defined datatypes are called as 'Type'.
Creating a Type
Syntax: CREATE TYPE <TYPE_NAME> AS OBJECT (<COLUMN_NAME> <DATATYPE>(<SIZE>), ... N);
Example: CREATE TYPE MYADDRESS AS OBJECT (PLOT NUMBER (4), CITY VARCHAR2 (10), COUNTRY VARCHAR2 (10), PIN NUMBER (6));
Above example will create a user-defined type called 'MYADDRESS'.
Note: To display the structure of a type, we can use the DESC command.
Using a Type in a Table
The example below shows how to use a type while creating a table in Oracle database.
CREATE TABLE EMP (EMPNO NUMBER(4), NAME VARCHAR2(10), SAL NUMBER(7, 2), ADDRESS MYADDRESS);
Inserting Data into a User-Defined Type Column
The example below shows how to insert data into a column having user-defined type.
INSERT INTO EMP( VALUES(1, 'ARNOLD', 5000, MYADDRESS(100, 'SYDNEY', 'AUSTRALIA', 17200));
Selecting Data from a User-Defined Type Column
The example below shows how to select data from a user-defined type column.
SELECT ENAME, E.EMPNO, E.ADDRESS.PLOT, E.ADDRESS.CITY, E.ADDRESS.COUNTRY, E.ADDRESS.PIN, FROM EMP;
Note: Table alias must be used to extract data from user defined type column.
Deleting a Type
We can use the DROP statement to delete a type as shown below:
DROP TYPE MYADDRESS;
Note: Types having table dependency cannot be deleted i.e. if a type is associated with a table it cannot be deleted. In order to delete a type the dependency should first be removed.