EXCEPTION logic is not working in Oracle PL/SQL function

functionsoracleplsql

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','starc@gmail.com','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','bruce@gmail.com','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','larry@gmail.com','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','steve@gmail.com','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','remy@gmail.com','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','clark@gmail.com','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 ?

Best Answer

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;