ORA-01422: Problem Solution

During PL SQL programming you may encounter an exception ORA-01422:exact fetch returns more than requested number of rows

Root Cause

You tried to execute a SELECT INTO statement which returns more than one row against where condition placed in. This SQL statement will throw an exception “ORA-01422:exact fetch returns more than requested number of rows” if employee table is having two rows with same employee name “ADAM”.

Begin
	  Select  salary into vSalary from Employee where empname='Adam';
         Exception when others then Dbms_output.put_line(SQLERRM); 
 End;

Solution

You need to Rewrite your SELECT INTO statement by modifying where clause and ensure that SQL command should always return unique row to avoid this exception.Refer the updated code below having additional condition in where clause now “emp_id = 1001”

Begin
      Select  salary into vSalary from Employee where empname = 'Adam' and emp_id = 1001 ;
      Exception when others then Dbms_output.put_line(SQLERRM);
End;

For more help,send us an email: info@techytraining.com