CREATE TABLE DEPARTMENT
(DEPARTMENT_ID NUMBER PRIMARY KEY,
 DEPARTMENT_NAME VARCHAR(30) NOT NULL
 );

 CREATE TABLE JOBS
 (JOB_ID NUMBER PRIMARY KEY,
  JOB_TITLE VARCHAR(35) NOT NULL,
  MIN_SALARY DECIMAL NOT NULL,
  MAX_SALARY DECIMAL 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 JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES (1,'IT Administrator',250000.00,50000.00);
INSERT INTO JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES (2,'Salesman',200000.00,40000.00);

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')

CREATE OR REPLACE FUNCTION fn_emps_per_dept_jc450912 (f_dept_id IN NUMBER,f_emp_id OUT NUMBER,f_first_name OUT VARCHAR,f_last_name OUT VARCHAR,f_job_title OUT VARCHAR)
RETURN BOOLEAN
AS
BEGIN
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_TITLE
INTO f_emp_id,f_first_name,f_last_name,f_job_title
FROM EMPLOYEES,JOBS,DEPARTMENT
WHERE DEPARTMENT.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
AND JOBS.JOB_ID = EMPLOYEES.JOB_ID
AND EMPLOYEES.DEPARTMENT_ID = f_dept_id;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN FALSE;
END fn_emps_per_dept_jc450912;

Function FN_EMPS_PER_DEPT_JC450912 compiled

DECLARE
f_dept_id NUMBER;
f_emp_id NUMBER;
f_first_name VARCHAR(30) ;
f_last_name VARCHAR(30) ;
f_job_title VARCHAR(30);
f_return BOOLEAN;
BEGIN
f_dept_id := 1;
f_return := fn_emps_per_dept_jc450912(f_dept_id,f_emp_id,f_first_name,f_last_name,f_job_title);
DBMS_OUTPUT.PUT_LINE('Employee_ID: ' || f_emp_id);
DBMS_OUTPUT.PUT_LINE('First Name: ' || f_first_name);
DBMS_OUTPUT.PUT_LINE('Last Name: ' || f_last_name);
DBMS_OUTPUT.PUT_LINE('Job: ' || f_job_title);
END;

Error report -
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYSTEM.FN_EMPS_PER_DEPT_JC450912", line 5
ORA-06512: at line 10
01422. 00000 -  "exact fetch returns more than requested number of rows"
*Cause:    The number specified in exact fetch is less than the rows returned.
*Action:   Rewrite the query or change number of rows requested