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

consider having the table listed below:

CREATE TABLE employee

   ( 
 emp_id,  
first_name VARCHAR2(50),   
salary FLOAT(126)   
 ) 
populated with many records (table can have a primary key etc.- out of scope)

If you try to execute the pl/sql below you should get an error

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

declare

name varchar2(40);

sal number;

begin

select salary into sal from

employee;

dbms_output.put_line(sal);

end;

This is because the sql query returns more than 1 rows. In order to get all records you could use:

declare

cursor c1 is

select first_name, salary from employee;

l_rec c1%rowtype;

begin

open c1;

loop

fetch c1 into l_rec;

exit when c1%NOTFOUND;

dbms_output.put_line(l_rec.first_name||' '||l_rec.salary);

end loop;

close c1;

end;

Results

George 1200 
Jenny 2300 
Julia 9000 
Mary 9000
Statement processed.