node-1
 |-node-1-1
   |-node-1-1-1
   |-node-1-1-2
     |-node-1-1-2-1
   |-node-1-1-3

declare
  not_exists_exception exception;
  pragma exception_init(not_exists_exception, -942);
begin
  begin
    execute immediate 'drop table node';
  exception
    when not_exists_exception then
      null;
  end;
  begin
    execute immediate 'drop table node_param';
  exception
    when not_exists_exception then
      null;
  end;
end;
/

create table node_param as
select 
    level id
  , dbms_random.string('A', trunc(dbms_random.value(1, 15))) val
  from dual
  connect by level <= 999
;
alter table node_param add constraint pk_node_param primary key (id);

create table node as
select 
    t.id
  , case
      when dbms_random.value < .4 
      then t.id
      else trunc(dbms_random.value(1, 99999))
    end node_id
  , trunc(dbms_random.value(1, 999)) param_id
  from (
    select level id
      from dual
      connect by level <= 99999
  ) t;
alter table node add constraint pk_node primary key (id);
alter table node add constraint fk_node_node_id foreign key (node_id) references node (id);
alter table node add constraint fk_node_param_id foreign key (param_id) references node_param (id);

create index idx_node_node_id on node (node_id);
create index idx_node_param_id on node (param_id);

create or replace function get_head_param(
  p_id in number
) return varchar2
is
  m_result varchar2(15);
begin
  select *
    into m_result
    from (
      select tp.val
        from node t
          join node_param tp
            on t.param_id = tp.id
        start with t.id = p_id
        connect by prior decode(t.node_id, t.id, null, t.node_id) = t.id
        order by level desc
    )
    where rownum < 2;
  return m_result;
exception
  when no_data_found then
    null;
end get_head_param;
/

select 
    t.id
  , get_head_param(t.id) tp_val
  from (
    select id, dbms_random.random
      from node
      order by 2
  ) t
  where rownum < 100;