ORA -01422 exact fetch returns more than one value

 ORA -01422 exact fetch returns more than one value

Ø Exact fetch returns more than one value occurs when the SELECT INTO statement return more than one values

Example:

1.  Declare

    P_deptno number;

    Begin  

         Select ename,job,sal

         Into P_ename, P_job, P_sal

         From emp e,dept d

         Where e.deptno=d.deptno

        And e.deptno = :p_deptno;

   End;

Output:

  p_deptno = 10

    ERROR;

      ORA -01422 exact fetch returns more than one value

Solution:

Ø Above select into statement return more than rows because dept = 10 had multiple employees in the emp table.

Ø To overcome this, we should use cursor.

 Below code can return one value,

1. Declare

         P_Ename varchar2(50);

    Begin  

         Select ename,job,sal

         Into P_ename, P_job, P_sal

         From emp e,dept d

         Where e.deptno=d.deptno

        And e.ename = :p_ename;

   End;

Output:

 p_ename = 'BLAKE'

 here ,Unique name of the employee return exact one row.

Share: