Oracle: Obtendo dados hierarquicamente

for rec in (select * from some_table) loop
  if FULLFILLS_START_WITH_CONDITION(rec) then
    RECURSE(rec, rec.child);
  end if;
end loop;
 
procedure RECURSE (rec in MATCHES_SELECT_STMT, parent_id IN field_type) is
  begin
  APPEND_RESULT_LIST(rec);     
  for rec_recurse in (select * from some_table) loop
    if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.id, parent_id) then
      RECURSE(rec_recurse,rec_recurse.id);
    end if;
  end loop;
end procedure RECURSE;

TRUNCATE TABLE PLAN_TABLE;
 
-- Gerando os dados do explain
EXPLAIN PLAN FOR
SELECT *
  FROM T_MEDICOS A,
       T_AGENDA B,
       T_HORARIO_AGENDA C
  WHERE A.NR_MEDICO = B.NR_MEDICO
    AND B.ID_AGENDA = C.ID_AGENDA;
 
-- Para mostrar o explain formatado
SELECT LPAD(' ', (LEVEL-1)*2) || OPERATION, NVL2(OPTIONS, '('||OPTIONS||')', ''),  OBJECT_NAME 
  FROM PLAN_TABLE
  START WITH ID = 0
  CONNECT BY PRIOR ID = PARENT_ID;

SELECT STATEMENT		
  HASH JOIN		
    HASH JOIN		
      TABLE ACCESS	(FULL)	T_MEDICOS
      TABLE ACCESS	(FULL)	T_AGENDA
    TABLE ACCESS	(FULL)	T_HORARIO_AGENDA