Object Technology in Oracle

 Oracle 8.0 - Object Oriented RDBMS Tool
 Object is a collection of elements of different data  types  stored at one location.   It will not hold data.
 It can have Methods ( Functions ) defined in it.
 It is stored permenantly in database in "User_types"   system table.
 It can be shared with other users.
 It is an re-usable component.
 It supports "Inheritence" feature of OOPS.

 8.0 -- Composite Datatypes            7.X - Comp DT Objects - C structure            Pl/sql Records
 Object with Methods - C++ class   Pl/sql Tables
 Nested Tables
 Varrying Arrays

 Syntax :
 create type <obj name > as object
 (element 1  datatype,
  element 2  datatype,
  .......
  element N datatype );

 Ex: 1 create type addr_type as object
        ( hno varchar2(10), street varchar2(20),
          city varchar2(20), pin number(6));
          /
 Ex: 2  create type pf_type as object
          (pfno number(4), amt number(10));
          /
 desc addr_type
 desc pf_type
 desc user_types
 select * from user_types;

 Ex : 1 Using Objects :
 create table student
  ( roll number(3),
   sname varchar2(20),
   address addr_type,
   course varchar2(20),
   fee number(5));

  desc student
  insert into student values(101,'RAM',
  addr_type('112 - A','ameerpet','hyd',500038),       'oracle',2500);

  select * from student;
  select sname, address from student;
  select sname, s.address.city, s.address.pin
  from student s;
  update student s set s.address.city = 'secbad'
  where roll  = 101;
  delete from student s where s.address.city = 'hyd';
---------------------------------------------------------------
 Ex :2 Re-using Object :
 create table emp(empno number(4),
 ename varchar2(20), eaddr addr_type,
 sal number(12,2), pf pf_type);

 desc emp
 insert into emp values(1001,'RAJ',
 addr_type('121- Q', 's.r.nagar', 'hyd', null), 12000,
 pf_type(123, 3000));

 select * from emp;
 select ename, eaddr from emp;
 select empno, sal, pf from emp;
 select ename, e.eaddr.city, e.eaddr.pin from emp   e;
 select empno, sal, e.pf.pfno from emp e;
 update emp e set e.pf.amt = 4000
 where e.pf.pfno = 123;
 update emp e set e.eaddr.pin = 500039
 where empno = 1001;
 delete from emp e where e.pf.pfno = 123;
---------------------------------------------------------------
 

 Sharing Objects : scott:  grant execute on addr_type to user1;

 user1:
 create table supplier ( supp_id number(4),
 sname varchar2(20), saddr scott.addr_type );

 insert into supplier(1001,'RAJ',
 scott.addr_type( '123-A' , 'ameerpet' , 'hyd' ,  500038));

 select * from supplier;
 desc supplier
 select sname,s.saddr.city from supplier s;
---------------------------------------------------------------
 

 Nested Objects : create type person_type as object
 ( pname varchar2(20), paddr addr_type);

 create table customer (cust_id number(3),
 cinfo person_type);

 desc customer
 insert into customer values( 101,
 person_type('RAM',addr_type(- - - -)));

 select * from customer;
 select cust_id , c.cinfo.pname,
 c.cinfo.paddr.city, c.cinfo.paddr.pin
 from customer c;
---------------------------------------------------------------
 

 Object Types: 2 Types
  Row Object:   Entire structure of table depends on object.

  Column Object:   Only few columns of table depends on
                   object.

  create type etype as object
  ( eid number(4), ename varchar2(20),
    sal number(10,2), deptno number(2));
  /
---------------------------------------------------------------
 

 Row Object : create table etab of etype;
 desc etab = desc etype
 insert into etab values(1001,'RAM',21000,10);
 select * from etab;
 select eid,ename,sal from etab;
 update etab set sal = sal + 4000;
---------------------------------------------------------------
 

 Column Object : create table employ(einfo etype, job varchar2(20),
 hiredate date);
 insert into employ  values(etype(101,'VARUN',21000,10),
 'MANAGER',sysdate);
 select * from employ;
 select e.einfo.eid, e.einfo.ename, e.einfo.sal, job
 from employ e;
---------------------------------------------------------------
 

Object with Methods:create or replace type etype as object
(ecode number(4), name varchar2(20),
basic number(12,2), job varchar2(20), hiredate date,
member function gross(vbasic number)
return number,
pragma restrict_references(gross,WNDS),
member function experiance(doj date)
return number,
pragma restrict_references(experiance,WNDS));
/

create or replace type body etype as
member function gross(vbasic number) return number is
begin
return(round(vbasic + vbasic * .25 + vbasic * .35 -
vbasic * .15));
end;
member function experiance(doj date)
return number is
begin
return(round(months_between(sysdate,doj))/12);
end;
end;
/

create table etab of etype; -- Row Object
insert into etab values(1001,'RAM',30000,'ANALYST',
'10-dec-01');
select ecode, name, job ,basic, e.gross(basic) "gross" ,
e.experiance(hiredate) "exp in years"  from etab e ;

Purity Level Test:Checks for member functions are having " DML" statements or not.
  * DML are not valid in member functions.
Restrict_references :  Built_in sub program
used to check for DML stmts in methods
    WNDS - Write No Database State
  * pragma - Instruction to Pl/sql
Supports to execute Sub program in declaration section.
---------------------------------------------------------------
 

* Objects cannot be Altered
* Constraints will not support on Objects
To apply restrictions on Object elements Triggers can be used :
>desc student
roll   number(3)
sname   varchar2(20)
address   addr_type
course   varchar2(20)
fee   number(5)

* Triggers checks for null value in city
create or replace trigger chk_city
before insert on student for each row
begin
if :new.address.city is null then
raise_application_error(-20500,'Student city cannot be Null');
end if;
end;
---------------------------------------------------------------

Removing Objects :
Drop type < obj name >;
> drop type addr_type;
> drop type pf_type;
* Object can be removed only if it is not used in any    table .
---------------------------------------------------------------