oracle objects and collections

Question

Just wanted to know if anyone is really using Objects and Collections in Oracle ? Is this something that should be avoided ?

eg

create type t_person as object (
  id integer,
  first_name varchar2(30),
  last_name varchar2(30)
);

Answer 1

I'm sure lots of programmers are using such extensions; personally I try to stick close to the "reasonably standard SQL" core in my use of relational DBs, because it's happened often that I need to port my SQL code to some other DB engine, and if I've splurged in using proprietary extensions the port becomes much harder (while the proprietary extensions often add very little functionality or speed anyway).

Answer 2

I use simple collections [ie a TABLE OF VARCHAR2(4000)] but only for 'working'. I've not actually created a table with any non-Oracle types. I have used the Oracle supplied objects (eg MDSYS.SDO_GEOM) for geographic stuff.

Answer 3

I've avoided them because objects do not play nicely with the relational model. If you have both you tend to get the power of neither. This is why object-relational mapping is such a big deal for projects.

Answer 4

If you are seriously into PL/SQL programming, you can hardly live without collections and objects. That said, I keep my database tables "clean", i.e. all columns contain atomic values, no nested tables etc.

Answer 5

Yes, we use collections as arguments to stored procs. That makes it possible to select all the records that have a certain id (often called variable in list) without using a temporary table.

For example:

create or replace type numbertype
as object(nr number(20,10) )
/ 

create or replace type number_table
as table of numbertype
/ 

create or replace 
procedure tableselect(p_numbers in number_table, p_ref_result out sys_refcursor)
is
begin  
   open p_ref_result for    
      select *    
      from employees 
      ,    (select /*+ cardinality(tab 10) */ tab.nr 
            from table(p_numbers) tab) tbnrs       
      where id = tbnrs.nr; 
end; 
/

You can also use this technique with mutli column objects.