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, '*');

    return;
  end number_typ;

end;
/
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;
begin

  begin
    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;
  end;

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

end;
/

The numbers and its translations are filled using the procedure:

begin
  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');
end;
/

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.


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