Heiarchal Retrieval W/Cursors

Here is my problem:

• Write a PLSQL anonymous block stored in file TASK3.SQL that does the
  following:
  
  • Displays in ascending last name order all employees who have other employees reporting
  • to them.
    
  • Asks the user for the last name and first name of one of those managers
    
  • Displays the level, last name and first name of the employees reporting to that manager.
  
  
  NOTE: To receive full credit, the code listing the employees reporting to a manager
  must be written with a CURSOR. You may receive up to 75% credit by writing the
  display as a simple SQL statement.
  

here are my employees:
EMPID, Last_Name, First_Name, Manager_id, Job_title, salary, dept_id

1, 'King', 'Gary', NULL, 'OWNER', 80000, 100);
2, 'Mont', 'Larry', 1, 'MANAGER', 50000, 200);
3, 'Poppines', 'Mary', 1, 'MANAGER', 60000, 300);
4, 'DOe', 'John', 2, 'SALESREP', 35000, 200);
5, 'Que', 'Sue', 2, 'ACCTCLERK', 30000, 200);
6, 'Phillapelli', 'Karen', 3, 'MECHANIC', 45000, 300);
7, 'Jungle', 'George', 3, 'PARTSCLERK', 32000, 300);



So this is what I have so far:

CREATE OR REPLACE FUNCTION get_manid
(p_last IN employee.last_name%TYPE,
p_first IN employee.first_name%TYPE)
RETURN NUMBER
IS
v_manager_id employee.manager_id%TYPE;
BEGIN
SELECT employee_id
INTO v_manager_id
FROM employee
WHERE last_name LIKE p_last
AND first_name LIKE p_first;

RETURN v_manager_id;
END get_manid;
/

DECLARE
CURSOR emp_cursor IS
SELECT LEVEL, last_name, first_name
FROM employee
START WITH employee_id = get_manid('&lastname', '&firstname')
CONNECT BY PRIOR employee_id = manager_id;

v_level NUMBER;
v_last VARCHAR2(15);
v_first VARCHAR2(15);

BEGIN
OPEN emp_cursor;

LOOP
FETCH emp_cursor INTO v_level, v_last, v_first;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('LEVEL: '|| v_level
||' Last Name: ' || v_last
||' First Name: ' || v_first);
END LOOP;
CLOSE emp_cursor;
END;
/



My problem: I can't ever get the right output. The way it is written right now shows all the employees and their levels. If I mess with it (I'm not sure what I'm doing), I can get it to show only the manager, which is not what I want. I want it to show, say if I chose the owner (KING), all the people that report to him (MONT POPPINS I believe) and their respective levels(2).

Any suggestions?

Your code works for me, perhaps the issue lies between the chair and the keyboard.

Code:

---

SQL> CREATE OR REPLACE FUNCTION Get_manid
  2      (p_last  IN employees.last_name%TYPE,
  3        p_first  IN employees.first_name%TYPE)
  4  RETURN NUMBER
  5  IS
  6    v_manager_id    employees.manager_id%TYPE;
  7  BEGIN
  8    SELECT employee_id
  9    INTO      v_manager_id
 10    FROM      employees
 11    WHERE  last_name LIKE p_last
 12          AND first_name LIKE p_first;
 13
 14    RETURN v_manager_id;
 15  END get_manid;
 16  /

Function created.

SQL> set serverout on size 1000000;
SQL> def lastname='King'
SQL> def firstname='Steven'
SQL> DECLARE
  2    CURSOR emp_cursor IS
  3      SELECT LEVEL,
  4            last_name,
  5            first_name
  6      FROM  employees
  7      START WITH employee_id = Get_manid('&lastname','&firstname')
  8      CONNECT BY PRIOR employee_id = manager_id;
  9    v_level  NUMBER;
 10    v_last  VARCHAR2(15);
 11    v_first  VARCHAR2(15);
 12  BEGIN
 13    OPEN emp_cursor;
 14
 15    LOOP
 16      FETCH emp_cursor INTO v_level,v_last,v_first;
 17
 18      EXIT WHEN emp_cursor%NOTFOUND;
 19
 20      dbms_output.Put_line('LEVEL: '
 21                          ||v_level
 22                          ||' Last Name: '
 23                          ||v_last
 24                          ||' First Name: '
 25                          ||v_first);
 26    END LOOP;
 27
 28    CLOSE emp_cursor;
 29  END;
 30  /
old  7:    START WITH employee_id = Get_manid('&lastname','&firstname')
new  7:    START WITH employee_id = Get_manid('King','Steven')

LEVEL: 1 Last Name: King First Name: Steven
LEVEL: 2 Last Name: Hartstein First Name: Michael
LEVEL: 3 Last Name: Fay First Name: Pat
LEVEL: 2 Last Name: Kochhar First Name: Neena
LEVEL: 3 Last Name: Whalen First Name: Jennifer
LEVEL: 3 Last Name: Mavris First Name: Susan
LEVEL: 3 Last Name: Baer First Name: Hermann
LEVEL: 3 Last Name: Higgins First Name: Shelley
LEVEL: 4 Last Name: Gietz First Name: William
LEVEL: 3 Last Name: Greenberg First Name: Nancy
LEVEL: 4 Last Name: Faviet First Name: Daniel
LEVEL: 4 Last Name: Chen First Name: John
LEVEL: 4 Last Name: Sciarra First Name: Ismael
LEVEL: 4 Last Name: Urman First Name: Jose Manuel
LEVEL: 4 Last Name: Popp First Name: Luis
LEVEL: 2 Last Name: De Haan First Name: Lex
LEVEL: 3 Last Name: Hunold First Name: Alexander
LEVEL: 4 Last Name: Ernst First Name: Bruce
LEVEL: 4 Last Name: Austin First Name: David
LEVEL: 4 Last Name: Pataballa First Name: Valli
LEVEL: 4 Last Name: Lorentz First Name: Diana
LEVEL: 2 Last Name: Raphaely First Name: Den
LEVEL: 3 Last Name: Khoo First Name: Alexander
LEVEL: 3 Last Name: Baida First Name: Shelli
LEVEL: 3 Last Name: Tobias First Name: Sigal
LEVEL: 3 Last Name: Himuro First Name: Guy
LEVEL: 3 Last Name: Colmenares First Name: Karen
LEVEL: 2 Last Name: Weiss First Name: Matthew
LEVEL: 3 Last Name: Nayer First Name: Julia
LEVEL: 3 Last Name: Mikkilineni First Name: Irene
LEVEL: 3 Last Name: Landry First Name: James
LEVEL: 3 Last Name: Markle First Name: Steven
LEVEL: 3 Last Name: Taylor First Name: Winston
LEVEL: 3 Last Name: Fleaur First Name: Jean
LEVEL: 3 Last Name: Sullivan First Name: Martha
LEVEL: 3 Last Name: Geoni First Name: Girard
LEVEL: 2 Last Name: Fripp First Name: Adam
LEVEL: 3 Last Name: Bissot First Name: Laura
LEVEL: 3 Last Name: Atkinson First Name: Mozhe
LEVEL: 3 Last Name: Marlow First Name: James
LEVEL: 3 Last Name: Olson First Name: TJ
LEVEL: 3 Last Name: Sarchand First Name: Nandita
LEVEL: 3 Last Name: Bull First Name: Alexis
LEVEL: 3 Last Name: Dellinger First Name: Julia
LEVEL: 3 Last Name: Cabrio First Name: Anthony
LEVEL: 2 Last Name: Kaufling First Name: Payam
LEVEL: 3 Last Name: Mallin First Name: Jason
LEVEL: 3 Last Name: Rogers First Name: Michael
LEVEL: 3 Last Name: Gee First Name: Ki
LEVEL: 3 Last Name: Philtanker First Name: Hazel
LEVEL: 3 Last Name: Chung First Name: Kelly
LEVEL: 3 Last Name: Dilly First Name: Jennifer
LEVEL: 3 Last Name: Gates First Name: Timothy
LEVEL: 3 Last Name: Perkins First Name: Randall
LEVEL: 2 Last Name: Vollman First Name: Shanta
LEVEL: 3 Last Name: Ladwig First Name: Renske
LEVEL: 3 Last Name: Stiles First Name: Stephen
LEVEL: 3 Last Name: Seo First Name: John
LEVEL: 3 Last Name: Patel First Name: Joshua
LEVEL: 3 Last Name: Bell First Name: Sarah
LEVEL: 3 Last Name: Everett First Name: Britney
LEVEL: 3 Last Name: McCain First Name: Samuel
LEVEL: 3 Last Name: Jones First Name: Vance
LEVEL: 2 Last Name: Mourgos First Name: Kevin
LEVEL: 3 Last Name: OConnell First Name: Donald
LEVEL: 3 Last Name: Grant First Name: Douglas
LEVEL: 3 Last Name: Rajs First Name: Trenna
LEVEL: 3 Last Name: Davies First Name: Curtis
LEVEL: 3 Last Name: Matos First Name: Randall
LEVEL: 3 Last Name: Vargas First Name: Peter
LEVEL: 3 Last Name: Walsh First Name: Alana
LEVEL: 3 Last Name: Feeney First Name: Kevin
LEVEL: 2 Last Name: Russell First Name: John
LEVEL: 3 Last Name: Tucker First Name: Peter
LEVEL: 3 Last Name: Bernstein First Name: David
LEVEL: 3 Last Name: Hall First Name: Peter
LEVEL: 3 Last Name: Olsen First Name: Christopher
LEVEL: 3 Last Name: Cambrault First Name: Nanette
LEVEL: 3 Last Name: Tuvault First Name: Oliver
LEVEL: 2 Last Name: Partners First Name: Karen
LEVEL: 3 Last Name: King First Name: Janette
LEVEL: 3 Last Name: Sully First Name: Patrick
LEVEL: 3 Last Name: McEwen First Name: Allan
LEVEL: 3 Last Name: Smith First Name: Lindsey
LEVEL: 3 Last Name: Doran First Name: Louise
LEVEL: 3 Last Name: Sewall First Name: Sarath
LEVEL: 2 Last Name: Errazuriz First Name: Alberto
LEVEL: 3 Last Name: Vishney First Name: Clara
LEVEL: 3 Last Name: Greene First Name: Danielle
LEVEL: 3 Last Name: Marvins First Name: Mattea
LEVEL: 3 Last Name: Lee First Name: David
LEVEL: 3 Last Name: Ande First Name: Sundar
LEVEL: 3 Last Name: Banda First Name: Amit
LEVEL: 2 Last Name: Cambrault First Name: Gerald
LEVEL: 3 Last Name: Ozer First Name: Lisa
LEVEL: 3 Last Name: Bloom First Name: Harrison
LEVEL: 3 Last Name: Fox First Name: Tayler
LEVEL: 3 Last Name: Smith First Name: William
LEVEL: 3 Last Name: Bates First Name: Elizabeth
LEVEL: 3 Last Name: Kumar First Name: Sundita
LEVEL: 2 Last Name: Zlotkey First Name: Eleni
LEVEL: 3 Last Name: Abel First Name: Ellen
LEVEL: 3 Last Name: Hutton First Name: Alyssa
LEVEL: 3 Last Name: Taylor First Name: Jonathon
LEVEL: 3 Last Name: Livingston First Name: Jack
LEVEL: 3 Last Name: Grant First Name: Kimberely
LEVEL: 3 Last Name: Johnson First Name: Charles

PL/SQL procedure successfully completed.

SQL>

---

:p

Quote:

---

Originally Posted by LKBrwn_DBA

perhaps the issue lies between the chair and the keyboard.

---

interface issue?