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?