PL SQL ORA-01422 exact fetch returns more than requested number of rows

Question

I am getting this error:

DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 11

Code.

DECLARE
    rec_ENAME EMPLOYEE.ENAME%TYPE;
    rec_JOB EMPLOYEE.DESIGNATION%TYPE;
    rec_SAL EMPLOYEE.SALARY%TYPE;
    rec_DEP DEPARTMENT.DEPT_NAME%TYPE;
BEGIN       
    SELECT EMPLOYEE.EMPID, EMPLOYEE.ENAME, EMPLOYEE.DESIGNATION, EMPLOYEE.SALARY,  DEPARTMENT.DEPT_NAME 
    INTO rec_EMPID, rec_ENAME, rec_JOB, rec_SAL, rec_DEP 
    FROM EMPLOYEE, DEPARTMENT 
    WHERE EMPLOYEE.SALARY > 3000;

    DBMS_OUTPUT.PUT_LINE ('Employee Nnumber: ' || rec_EMPID);
    DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE ('Employee Name: ' || rec_ENAME);
    DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE ('Employee Designation: ' || rec_JOB);
    DBMS_OUTPUT.PUT_LINE ('----------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE ('Employee Salary: ' || rec_SAL);
    DBMS_OUTPUT.PUT_LINE ('----------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE ('Employee Department: ' || rec_DEP);

END;
/

Can someone please help me with this?

Answer 1

If a SELECT INTO command returns more than one row, an error will be raised. You will receive a no_data_found exception if it returns zero rows. An exception titled too many rows will be thrown if it returns more than one row. You don't want a SELECT INTO statement here unless you are certain that there will always be one employee whose income is higher than $3,000 per year. Assuming you meant to perform a proper join between the two tables rather than a Cartesian product, and that there is a departmentID field in both tables, you most certainly want to use a cursor to traverse over (perhaps) many rows of data.

BEGIN
  FOR rec IN (SELECT EMPLOYEE.EMPID, 
                     EMPLOYEE.ENAME, 
                     EMPLOYEE.DESIGNATION, 
                     EMPLOYEE.SALARY,  
                     DEPARTMENT.DEPT_NAME 
                FROM EMPLOYEE, 
                     DEPARTMENT 
               WHERE employee.departmentID = department.departmentID
                 AND EMPLOYEE.SALARY > 3000)
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Employee Nnumber: ' || rec.EMPID);
    DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE ('Employee Name: ' || rec.ENAME);
    DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE ('Employee Designation: ' || rec.DESIGNATION);
    DBMS_OUTPUT.PUT_LINE ('----------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE ('Employee Salary: ' || rec.SALARY);
    DBMS_OUTPUT.PUT_LINE ('----------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE ('Employee Department: ' || rec.DEPT_NAME);
  END LOOP;
END;

I'm assuming that you are just learning PL/SQL as well. In real code, you'd never use dbms_output like this and would not depend on anyone seeing data that you write to the dbms_output buffer.