utl_ref

procedure delete_object (
 ref                                of                      ref standard
);

procedure lock_object (
 ref                                of                      ref standard
);

procedure lock_object (
 ref                                of                      ref standard,
 object                            out                 standard        
);

procedure select_object (
 ref                                of                      ref standard,
 object                            out                 standard        
);

procedure select_object_with_cr (
 ref                                of                      ref standard,
 object                            out                 standard        
);

procedure update_object (
 ref                                of                      ref standard,
 object                             in                 standard        
);

create type obj_ex as object (
  id number,
  name varchar2(10),
  member procedure set_(i in number, n in varchar2),
  member procedure get_(i out number, n out varchar2)
)
/

create type body obj_ex as

  member procedure set_(i in number, n in varchar2) is begin
    id   := i;
    name := n;
  end set_;
  
  member procedure get_(i out number, n out varchar2) is begin
    i := id;
    n := name;
  end get_;
  
end;
/

create table obj_t of obj_ex;

insert into obj_t values (obj_ex(1, 'one' ));
insert into obj_t values (obj_ex(2, 'two' ));
insert into obj_t values (obj_ex(3, 'three'));

create procedure obj_p(o_ in ref obj_ex) as
  i number;
  n varchar2(10);
  
  o obj_ex;
begin
  
  utl_ref.select_object(o_, o);

  o.get_(i, n);
  o.set_(i*10, upper(n));

  utl_ref.update_object(o_, o);
  
end obj_p;
/

declare
  o_ ref obj_ex;
begin

  for r in (select ref(t) ref_o from obj_t t) loop
    obj_p(r.ref_o);
  end loop;

end;
/

select * from obj_t;