Oracle PL/SQL/Object Oriented Database/treat

Treat type as

 
SQL>
SQL>
SQL> create or replace
  2  type person as object (
  3   first_name varchar2(100),
  4   last_name varchar2(100),
  5   dob date,
  6   phone varchar2(100),
  7   member function get_last_name return varchar2,
  8   member function get_phone_number return varchar2 )
  9  not final
 10  /
Type created.
SQL>
SQL>
SQL> create or replace
  2  type body person as
  3    member function get_last_name return varchar2 is
  4    begin
  5      return self.last_name;
  6    end;
  7    member function get_phone_number return varchar2 is
  8    begin
  9      return self.phone;
 10    end;
 11  end;
 12  /
Type body created.
SQL>
SQL> create table person_table( p person );
Table created.
SQL>
SQL>
SQL>
SQL> create or replace
  2  type new_employee under person (
  3    empno number,
  4    hiredate date,
  5    work_phone varchar2(100),
  6    overriding member function get_phone_number return varchar2,
  7    member function get_home_phone_number return varchar2 )
  8  not final
  9  /
Type created.
SQL>
SQL> create or replace
  2  type body new_employee as
  3    overriding member function get_phone_number return varchar2 is
  4    begin
  5      return self.work_phone;
  6    end;
  7    member function get_home_phone_number return varchar2 is
  8    begin
  9      return self.phone;
 10    end;
 11  end;
 12  /
Type body created.
SQL>
SQL>
SQL> select treat( x.p as new_employee ).empno empno,
  2         x.p.last_name last_name
  3     from person_table x
  4    where p is of ( new_employee )
  5  /
no rows selected
SQL>
SQL> select * from person_table;
no rows selected
SQL>
SQL> drop table person_table;
Table dropped.
SQL>
SQL> drop type new_employee;
Type dropped.
SQL>
SQL> drop type person;
Type dropped.
SQL>
SQL>

Use treat to convert varible to a type in select statement

 
SQL>
SQL>
SQL>
SQL> create or replace
  2  type person as object (
  3   first_name varchar2(100),
  4   last_name varchar2(100),
  5   dob date,
  6   phone varchar2(100),
  7   member function get_last_name return varchar2,
  8   member function get_phone_number return varchar2 )
  9  not final
 10  /
Type created.
SQL>
SQL>
SQL> create or replace
  2  type body person as
  3    member function get_last_name return varchar2 is
  4    begin
  5      return self.last_name;
  6    end;
  7    member function get_phone_number return varchar2 is
  8    begin
  9      return self.phone;
 10    end;
 11  end;
 12  /
Type body created.
SQL>
SQL> create table person_table( p person );
Table created.
SQL>
SQL> select treat( p as person).get_last_name() from person_table p;
no rows selected
SQL>
SQL> drop table person_table;
Table dropped.
SQL>
SQL> drop type person;
Type dropped.
SQL>