Returning values from 2 select statements in Oracle Procedure

Homework question that I’m stuck on.

I need to create a Procedure called dept_info with a department id as the IN parameter with department name and full name (concat of first and last name) of the manager of that department as the OUT parameters to be passed back to the invoking program. the procedure should be within the PL SQL block.

There are two tables to be used: departments which holds the department ID, department name, and manager ID, and employees, which holds employee ID (which would be the same as manager ID), first name, and last name.

The executable section should print out the info for department ID 100.

My code so far is:

DECLARE
    dept_id departments.department_id%TYPE;
    dept_name VARCHAR(30);
    mgr_id NUMBER(4);
    full_name VARCHAR(100);

    PROCEDURE dept_info(
    dep_id IN departments.department_id%TYPE,
    dep_name OUT departments.department_name%TYPE,
    full_name OUT employees.first_name%TYPE)
    IS
    BEGIN
        SELECT department_name, manager_id INTO dept_name, mgr_id FROM DEPARTMENTS WHERE department_id = dept_id;
        SELECT first_name || ' ' || last_name AS full_name INTO full_name FROM EMPLOYEES WHERE employee_id = mgr_id;
    END dept_info;
BEGIN
    dept_info(100, dept_name, full_name);
    DBMS_OUTPUT.PUT_LINE(dept_id || dept_name || full_name);
END;
    

This returns a “no data found” which means my data from my SELECT and INTO statements are not working properly. Any help to point me in the right direction is appreciated.

Answer

More clear naming for the variables would be helpful, but in the end I used the improper variable in the SELECT statements. Correct code:

DECLARE
    dept_id departments.department_id%TYPE := 100;
    dept_name VARCHAR(30);
    mgr_id NUMBER(4);
    full_name VARCHAR(46);
    PROCEDURE dept_info(
    dep_id IN departments.department_id%TYPE,
    dep_name OUT departments.department_name%TYPE,
    full_name OUT employees.first_name%TYPE)
    IS
    BEGIN
        SELECT department_name, manager_id INTO dep_name, mgr_id FROM DEPARTMENTS WHERE department_id = dep_id;
        SELECT first_name || ' ' || last_name AS full_name INTO full_name FROM EMPLOYEES WHERE employee_id = mgr_id;
    END dept_info;
BEGIN
    dept_info(100, dept_name, full_name);
    DBMS_OUTPUT.PUT_LINE(dept_id || dept_name || full_name);
END;