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

Fashion

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.

Vaduz

Thanks, but I had to keep all of those "DBMS_OUTPUT" lines and then use "SET SERVEROUTPUT ON" to display the results. What would the correct way be? The book that I am learning from is teaching me to do it this way. There is a departmentID column in both tables but I needed the departmentName which was only in the department table. Yes I am new to PL/SQL, and I am learning it as part of my database programming class for my undergraduate major in information systems. – Fashion

It is not that DBMS_OUTPUT is incorrect, but what instructors and tutorial writers just kind of bypass. Dbms_Output does not produce any output. It creates a buffer that the client can processed (read) whether that client is sqlplus, an IDE, a java app/program, a web service, ... Likewise the client can just ignore it. The problem is most production app/services just ignore it. It does allow to create message to show you what your process is doing, and values. Thus it is a useful learning and debugging tool. But not much use in a live app. – Raja

For additional information see DBMS_OUTPUT documentation and/or search for dbms_output.get_line. – Raja

Answer 2

Always remember whenever you are using select * into statement along with type or rowtype select into statement should be fetching exact one record otherwise select into statement will clause this error. In order to have multiple record you can use cursors or for loop to iterate.

Hereld