Creating Object type and usage : Example-I

You  can create object types with the CREATE TYPE and the CREATE TYPE BODY statements. The CREATE TYPE statement specifies the name of the object type, its attributes, methods, and other properties. The CREATE TYPE BODY statement contains the code for the methods that implement the type.

bkd@Oracle>CREATE OR REPLACE TYPE Studentobj as object (
  2  id number,
  3  dob date,
  4  member function getage return number);
  5  /

Type created.

If the a type contains member functions/procedures, the the procedural work needs to be defined in the TYPE BODY(similar to create specification and package BODY)

bkd@Oracle>ed
Wrote file afiedt.buf

  1  create or replace type body studentobj as
  2     member function getage return number as /* subprogram associated with the object type that is referenced as an attribute*/
  3     begin
  4     return trunc(months_between(sysdate , dob)/12);
  5     end;
  6* end;
bkd@Oracle>/

Type body created.

bkd@Oracle>create table student
  2  ( roll number,
  3    studentinf studentobj);

Table created.

bkd@Oracle>insert into student values( 1, studentobj( 123,sysdate-365*5));

1 row created.

bkd@Oracle>select p.id , 

                              p.studentinf.roll, 

                              p.studentinf.dob, 

                              p.studentinf.getage() /* method constructor  studentinf.getage()*/
                     from student p;

        ID STUDENTINF.ROLL STUDENTIN P.STUDENTINF.GETAGE()
---------- ------------- --------- ------------------------------------------------------------------
         1                      123       25-AUG-07                     4

Regards

BKD

Getting distinct values from a column of data type CLOB

-- What I have  learnt today:  
bkd@Oracle>drop table t;

Table dropped.

bkd@Oracle>create table t
  2  as
  3
bkd@Oracle>create table t (col clob);

Table created.

bkd@Oracle>insert into t
  2  (select 'A' from dual
  3  UNION ALL
  4  select 'B' from dual
  5  union all
  6  select 'C' from dual
  7  union all
  8  select 'A' from dual );

4 rows created.

bkd@Oracle>select col from t;

COL
--------------------------------------------------------------------------------
A
B
C
A

bkd@Oracle>--Remove duplicate--
bkd@Oracle>select distinct col from t;
select distinct col from t
                *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
You can not use DISTINCT in column of data type CLOB.So, how we are going to get the distinct values out of it?

Workaround:

bkd@Oracle>select a.col from t a
  2  where not exists ( select 1 from t b
  3                     where a.rowid < b.rowid
  4                     and dbms_lob.compare(a.col,b.col)=0);

COL
--------------------------------------------------------------------------------
B
C
A

Regards
BKD