EXCEPTION logic is not working in Oracle PL/SQL function

Question

These are the tables I have created and inserted some values:

CREATE TABLE DEPARTMENT
(DEPARTMENT_ID NUMBER PRIMARY KEY,
 DEPARTMENT_NAME VARCHAR(30) NOT NULL
 );
CREATE TABLE EMPLOYEES
(EMPLOYEE_ID NUMBER PRIMARY KEY,
 FIRST_NAME VARCHAR(20) NOT NULL,
 LAST_NAME VARCHAR(25) NOT NULL,
 EMAIL VARCHAR(25) NOT NULL,
 PHONE_NUMBER VARCHAR(20) NOT NULL,
 HIRE_DATE DATE NOT NULL,
 JOB_ID NUMBER NOT NULL,
 SALARY DECIMAL NOT NULL,
 DEPARTMENT_ID NUMBER NOT NULL,
 CONSTRAINT emp_job_fk FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID),
 CONSTRAINT emp_department_fk FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID)
 );
INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(1,'IT');
INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(2,'Sales');
INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(3,'Accounting');
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (1,'Tony','Starc','[email protected]','0123456789',TO_DATE('15/1/2008','DD/MM/YYYY'),1,45000.00,1);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (2,'Bruce','Wayne','[email protected]','0123456788',TO_DATE('15/1/2009','DD/MM/YYYY'),1,40000.00,1);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (3,'Larry','Ellison','[email protected]','0123456787',TO_DATE('15/1/2010','DD/MM/YYYY'),1,30000.00,1);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (4,'Steve','Jobs','[email protected]','0123456786',TO_DATE('15/1/2011','DD/MM/YYYY'),2,35000.00,2);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (5,'Remy','Lebeau','[email protected]','0123456785',TO_DATE('15/1/2012','DD/MM/YYYY'),2,30000.00,2);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (6,'Clark','Kent','[email protected]','0123456784',TO_DATE('15/1/2013','DD/MM/YYYY'),2,20000.00,2);

Then I have created a function to display number of employees working in a specific department:

CREATE or REPLACE FUNCTION GET_EMP_COUNT_JC450912 (dept_name VARCHAR)
RETURN VARCHAR 
IS
no_of_employees NUMBER;
BEGIN
SELECT COUNT(*) INTO no_of_employees
FROM DEPARTMENT, EMPLOYEES
WHERE DEPARTMENT.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
AND DEPARTMENT_NAME = dept_name;
DBMS_OUTPUT.PUT_LINE ('No of employees');
RETURN no_of_employees;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Department Not Available');
END GET_EMP_COUNT_JC450912;
BEGIN
DBMS_OUTPUT.PUT_LINE(GET_EMP_COUNT_JC450912('IT'));
END;
DBMS OUTPUT:
No of employees
3
BEGIN
DBMS_OUTPUT.PUT_LINE(GET_EMP_COUNT_JC450912('Sales'));
END;
DBMS OUTPUT:
No of employees
3

As you can see from the code above the function is successfully displaying number of employees working in a specific department when I'm entering valid department name. However, the problem that I'm facing is when I'm entering an invalid department name (that doesn't exist in department table), it is supposed show the error message 'Department Not Available' as I have set [EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Department Not Available');]

Instead I'm getting this message when I'm entering invalid department name:

BEGIN
DBMS_OUTPUT.PUT_LINE(GET_EMP_COUNT_JC450912('Medicine'));
END;
DBMS OUTPUT:
No of employees
0

Where could I possibly go wrong ?

Answer 1

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 > 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;

Answer 2

This code could throw the no_data_found error. You should avoid using "WHEN OTHERS", since you may not know exactly what caused the exception.

CREATE or REPLACE FUNCTION GET_EMP_COUNT_JC450912
 ( p_dept_name VARCHAR )
RETURN VARCHAR
IS
 no_of_employees NUMBER;
BEGIN
 SELECT department.department_name
 INTO no_of_employees
 FROM department
 INNER JOIN employees
 WHERE department.department_id = employees.department_id
 AND department.department_name = p_dept_name;
 DBMS_OUTPUT.PUT_LINE ('No of employees');
 RETURN no_of_employees;
EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Department Not Available');
END GET_EMP_COUNT_JC450912;
/

Answer 3

count(*) will never return the NO_DATA_FOUND exception. It is not supposed to return error if found no rows. It gives 1 row with result 0 (zero)

• stranger: By the way, I didn't use NO_DATA_FOUND. Rather I have used OTHERS instead of NO_DATA_FOUND. You may have a look at my function code again. On the other hand, what can be the possible fix to the problem that I am facing ?
• SQLDevDBA: Like he said, count will never return NO_DATA_FOUND, which is an exception. OTHERS is just a way of saying "DEFAULT EXCEPTION." Since your query does not return an exception, it will never trigger the exception block. If you want to trigger it, try selecting a column into the no_of_employees variable instead of a COUNT.