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

Question

I get keep getting this error I can't figure out what is wrong.

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

Here is my 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; / 

Answer 1

A SELECT INTO statement will throw an error if it returns anything other than 1 row. If it returns 0 rows, you'll get a no_data_found exception. If it returns more than 1 row, you'll get a too_many_rows exception. Unless you know that there will always be exactly 1 employee with a salary greater than 3000, you do not want a SELECT INTO statement here.

Most likely, you want to use a cursor to iterate over (potentially) multiple rows of data (I'm also assuming that you intended to do a proper join between the two tables rather than doing a Cartesian product so I'm assuming that there is a departmentID column in both tables)

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.