SQL & PL/SQL

I am trying to work out some SQL to generate an HR hierarchy list for a member of staff. Each member of staff can have a supervisor, as per the common employee / supervisor setup that happens all over the place.

This is a sample:

SELECT papf1.full_name leve1_full_name
     , papf1.employee_number level1_empno
     , papf2.full_name leve2_full_name
     , papf2.employee_number level2_empno
  FROM hr.per_all_people_f papf1
     , hr.per_all_assignments_f paaf1
     -- --------------------------------------------------- level2 tables
,      hr.per_all_assignments_f paaf2
     , hr.per_all_people_f papf2
-- -------------------------------------------------------- level1 joins
WHERE  papf1.person_id = paaf1.person_id
   AND paaf1.supervisor_id = papf2.person_id(+)
   -- ----------------------------------------------------- level2 joins
   AND papf2.person_id = paaf2.person_id
-- -------------------------------------------------------- paaf date stuff
   AND SYSDATE BETWEEN paaf1.effective_start_date
                   AND NVL(paaf1.effective_end_date, SYSDATE + 1)
   AND SYSDATE BETWEEN paaf2.effective_start_date
                   AND NVL(paaf2.effective_end_date, SYSDATE + 1)
-- -------------------------------------------------------- papf date stuff
   AND SYSDATE BETWEEN NVL(papf1.effective_start_date, SYSDATE - 1)
                   AND NVL(papf1.effective_end_date, SYSDATE + 1)
   AND SYSDATE BETWEEN NVL(papf2.effective_start_date, SYSDATE - 1)
                   AND NVL(papf2.effective_end_date, SYSDATE + 1)
   AND paaf1.primary_flag = 'Y'
   AND paaf1.assignment_type = 'E'
   AND papf1.current_employee_flag = 'Y'
   AND NVL(papf2.current_employee_flag, 'Y') = 'Y'
   AND papf1.employee_number = 1234;

-----------------------------------------------------------------------------------
| level1_full_name      | level1_empno   | level2_full_name      | level2_empno   |
-----------------------------------------------------------------------------------
| JONES, BOB            | 123            | HARRIS, TOM           | 1234
-----------------------------------------------------------------------------------

This is sort of what I'm after - but of course it only goes 1 level down.

I would really like to just enter in an empno for an employee, and generate a list of managers who are in the person's HR chain.

I tried this SQL, using CONNECT BY PRIOR, but it takes ages and ages to run, and does not complete. Am I doing something silly with it - I can't see anything, but I'm no expert:

SELECT     papf1.full_name leve1_full_name
         , papf1.employee_number level1_empno
         , papf2.full_name leve2_full_name
         , papf2.employee_number level2_empno
      FROM hr.per_all_people_f papf1
         , hr.per_all_assignments_f paaf1
         , hr.per_all_assignments_f paaf2
         , hr.per_all_people_f papf2
     WHERE papf1.person_id = paaf1.person_id
       AND paaf1.supervisor_id = papf2.person_id
       AND papf2.person_id = paaf2.person_id
       AND SYSDATE BETWEEN paaf1.effective_start_date
                       AND NVL(paaf1.effective_end_date, SYSDATE + 1)
       AND SYSDATE BETWEEN paaf2.effective_start_date
                       AND NVL(paaf2.effective_end_date, SYSDATE + 1)
       AND SYSDATE BETWEEN NVL(papf1.effective_start_date, SYSDATE - 1)
                       AND NVL(papf1.effective_end_date, SYSDATE + 1)
       AND SYSDATE BETWEEN NVL(papf2.effective_start_date, SYSDATE - 1)
                       AND NVL(papf2.effective_end_date, SYSDATE + 1)
       AND paaf1.primary_flag = 'Y'
       AND paaf1.assignment_type = 'E'
       AND papf1.current_employee_flag = 'Y'
       AND NVL(papf2.current_employee_flag, 'Y') = 'Y'
       AND papf1.EMPLOYEE_NUMBER = 123
CONNECT BY PRIOR paaf1.supervisor_id = papf2.person_id;

Thanks

Jim