EXCEPTION logic is not working in Oracle PL/SQL function
You are trying to find an error where an error can not exist. When you are doing a count it will always return a valid result even if the value that you are looking for does not exist in the table.

What you need to do is two separate counts and do a check to see what the return value is.

SELECT COUNT(*) INTO no_of_departments
FROM    DEPARTMENT
WHERE   DEPARTMENT_NAME = dept_name;

IF no_of_departments = 0 THEN
    * ERROR LOGIC HERE *
END IF;

IF no_of_departments &gt; 1 THEN
    * ERROR LOGIC HERE *
END IF;

SELECT COUNT(*) INTO no_of_employees
FROM    DEPARTMENT, EMPLOYEES
WHERE   DEPARTMENT.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
AND DEPARTMENT_NAME = dept_name;

IF no_of_employees = 0 THEN
    * ERROR LOGIC HERE *
END IF;