How to Manage ORACLE OBJECTS and OBJECT TYPES

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;

Objects with Member functions

You can also create objects with member functions define within the object.

For example the following statement creates an object with member functions

create type stud as object (

          maths number(3),

          phy   number(3),

          chem  number(3),

          member function tot return number,

          member function result return varchar2);

You have to define the MEMBER FUNCTION in OBJECT BODY. The following statement creates  object body of STUD object

create type body stud as (

     member function tot return number is

begin

     return(maths+phy+chem);

end;

     member function result return varchar2 is

              t number(3);

          begin

              t:=maths+phy+chem;

              if maths < 35 or phy < 35 or chem < 35 then

                   return(‘Fail’);

              elsif t < 150 then

                   return(‘Third’);

              elsif t >= 150 and t <= 180 then

                   return(‘Second’);

              else

                   return (‘First’);

              end if;

          end;

end;

Now you can use this object in defining students table

Create table students (rollno number(5),

              Name varchar2(20),

              Marks stud);

To insert rows into students table give the following statement

Insert into students values (101,’Sami’,stud(99,88,98));

To select the rows from students table, give the following statement.

select s.rollno,s.name,s.marks.maths,s.marks.phy,

              s.marks.chem,s.marks.tot(),s.marks.result()

          from students s;

Varrays

An array is an ordered set of data elements. All elements of a given array are of the same datatype. Each element has an index, which is a number corresponding to the element's position in the array.

The number of elements in an array is the size of the array. Oracle allows arrays to be of variable size, which is why they are called varrays. You must specify a maximum size when you declare the array type.

For example, the following statement declares an array type:

CREATE TYPE phones AS VARRAY(10) OF varchar2(10);

To use this varray in table, give the CREATE TABLE statement as follows

Create table suppliers (supcode number(5),

              Company varchar2(20),

              ph   phones);

To insert rows

insert into suppliers values (101,’Interface Computers’,

          Phones(‘64199705’,’55136663’));

insert into suppliers values (102,’Western Engg. Corp’,

          Phones(‘23203945’,’23203749’,’9396577727’));

To select rows

Select * from suppliers;

Listing Information about Objects

To see object tables in your schema, give the following statement.

select * from user_object_tables;

To select object types defined in your schema, give the following statement.

select * from user_types;