ORACLE OBJECTS AND OBJECT TYPES

Oracle object types are user-defined data types that make it possible to model complex real-world entities such as customers and purchase orders as unitary entities --"objects"--in the database.

Oracle object technology is a layer of abstraction built on Oracle's relational technology. New object types can be created from any built-in database types and/or any previously created object types, object references, and collection types. Meta-data for user-defined types is stored in a schema that is available to SQL, PL/SQL, Java, and other published interfaces.

Object types and related object-oriented features such as variable-length arrays and nested tables provide higher-level ways to organize and access data in the database. Underneath the object layer, data is still stored in columns and tables, but you are able to work with the data in terms of the real-world entities--customers and purchase orders, for example--that make the data meaningful. Instead of thinking in terms of columns and tables when you query the database, you can simply select a customer.

For example you use address which comprises of streetno, locality, city, pin, country, in many tables. You define these columns in each table separately like


     Create table emp (empno number(5),Name varchar2(20),Sal number(10,2),Streetno varchar2(20),Locality varchar2(20),City varchar2(20),Pin varchar2(7),Country varchar2(20));

     Create table customers (custcode number(5),Name varchar2(20),Bal number(10,2),Streetno varchar2(20),Locality varchar2(20),City varchar2(20),Pin varchar2(7),Country varchar2(20));

Instead of defining address columns again and again in each table, you can define an address datatype and then use this user defined datatype in all tables.

Defining an Object Type


The following example create an object type

     Create type address as object ( Streetno varchar2(20),Locality varchar2(20),City varchar2(20),Pin varchar2(7),Country varchar2(20));

Now you can use this datatype while defining tables emp and customer like this

    Create table emp (empno number(5),Name varchar2(20),Sal number(10,2),Emp_addr address);

    Create table customers (custcode number(5),Name varchar2(20),Bal number(10,2),Cust_addr address);

Inserting Records in tables with object datatypes.
To insert record into emp table you have to give the following statement.
    Insert into emp values (101,’Sami’,2000,address(‘A.C.Gaurds Road’,’A.C.Gaurds’,’Hyd’,’500004’,’India’);

To update city of employee number 101 you have to give the following update statement
Update emp e set e.emp_addr.city=’Bombay’ where e.empno=101;

To select rows from emp table give the following statement
Select * from emp;
To select only empno,name and city give the following statement
Select e.empno, e.name, e.emp_addr.city from emp e;