Oracle deref object from objects table

Question

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?

Answer 1

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