oracle-databaseplsqlobject-relational-model

Oracle deref object from objects table


I would like to know what is the best way to get an object from an Oracle object table into a deref variable.

This is my actual method, lets suppose the type:

create or replace TYPE person AS OBJECT (
  dni            VARCHAR2(20),
  nom            VARCHAR2(20),
  MAP MEMBER FUNCTION get_dni RETURN varchar2
);

create or replace TYPE BODY person AS
  MAP MEMBER FUNCTION get_dni RETURN varchar2 IS
  BEGIN
    RETURN dni;
  END;
END;

With this objects table:

create table partners of person;

Then, to get a person into a var I do:

declare
    a_person person;
begin
    select deref(ref(p)) into a_person
      from partners p 
     where p.dni = 1;
    ...
end;

Is there a more elegant way to get unref object?


Solution

  • Seems that there are no really elegant alternative for your style of table declaration.

    Only variant below, but it seems to be less elegant than yours:

    declare
      a_person person;
    begin
      select person(p.dni, p.nom) into a_person
      from partners p 
      where p.dni = 1;
    end;
    

    I.e. decompose object to fields and construct it back.

    But if you declare table with object type field, e.g.

    create table new_partners (pers person)
    

    then access would be much simpler:

    declare
      a_person person;
    begin
      select p.pers into a_person
      from new_partners p 
      where p.pers.dni = 1;
    end;
    

    SQLFiddle