René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -

August 16, 2005: On storing objects in tables

In this article, I want to demonstrate how to achieve a parent-child relationship with PL/SQL objects stored in tables.
First, I create an object that will act as the parent:
create type number_typ as object (
  num     number(2),
  stars   varchar2(20),

  constructor function number_typ(n in number) return self as result

create type body number_typ as 
  constructor function number_typ(n in number) return self as result is begin
    num   := n;
    stars := lpad('*', n, '*');

  end number_typ;

show errors
The next object will act as the children. That is, one number can have multiple translations. This is achieved through the ref modifier:
create type translation_typ as object (
  trs     varchar2(20),
  lang    char(2),
  num_ref ref number_typ
I also create two seperate tables, one for each of my types:
create table number_tab      of number_typ;
create table translation_tab of translation_typ;
The following function will add a number's translation to the number. If the number is not yet stored in the table, it will first insert the number:
create procedure insert_number (p_num in number, p_trans in varchar2, p_lang in char) as
   v_number ref number_typ;

    select ref(n) into v_number from number_tab n where num = p_num;

  exception when no_data_found then
    insert into number_tab n values (number_typ(p_num)) returning ref(n) into v_number;

  insert into translation_tab values (p_trans, p_lang, v_number);

The numbers and its translations are filled using the procedure:
  insert_number(1, 'one'  , 'en');
  insert_number(2, 'two'  , 'en');
  insert_number(3, 'trois', 'fr');
  insert_number(4, 'vier' , 'de');
  insert_number(4, 'four' , 'en');
  insert_number(1, 'eins' , 'de');
  insert_number(3, 'drei' , 'de');
  insert_number(2, 'deux' , 'fr');
Finally, I can show the real cool feature about objects stored in tables.
I clearly select across two tables: translation_tab and number_tab. However, the from clause only names the child's table (translation_tab). The object stored in that table knows about its parent (through the ref modifier, remember).
select t.num_ref.num, t.num_ref.stars, t.trs, t.lang 
  from translation_tab t
 where t.num_ref.num between 2 and 3;
Here's the output:
NUM_REF.NUM TRS                  LA
----------- -------------------- --
          2 two                  en
          3 trois                fr
          3 drei                 de
          2 deux                 fr

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.