Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams 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);
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)
    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;
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.
                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.
– Hiram
                Nov 5, 2013 at 1:16
                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.
– Belayer
                Feb 9, 2021 at 17:56
                For additional information see DBMS_OUTPUT documentation and/or search for dbms_output.get_line.
– Belayer
                Feb 9, 2021 at 17:58
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.