ORA-01422: exact fetch returns more than requested number of rows
Error
ORA-01422: exact fetch returns more than requested number of rows
SQL> Declare
2 l_id NUMBER;
3 BEGIN
4 SELECT id INTO l_id FROM test1 ;
5 /* do something with emp data */
6 END;
7 /
Declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4
Cause
Fetching multiple rows during the PLSQL package, we need to get one row at a time.
Solution
For fetching or handling multiple rows we need to use the cursor in PLSQL or used where clause so that one row is processed otherwise getting the error.
In example, we use where clause so that it return the one rows. we can use cursor if we want to handle all data of table one by one.
SQL> select * from test1;
ID NAME
---------- ----------
1 RAM
2 sham
3 POOJA
-- Use where clause to handle one row.
SQL> Declare
2 l_id NUMBER;
3 BEGIN
4 SELECT id INTO l_id FROM test1 WHERE name = 'RAM';
5 /* do something with emp data */
6 END;
7 /
PL/SQL procedure successfully completed.
--Use Cursor for handle error
set serveroutput on
DECLARE
l_id NUMBER;
l_name varchar2(20);
CURSOR c_fetchname is SELECT id,name FROM test1;
BEGIN
OPEN c_fetchname;
LOOP
FETCH c_fetchname into l_id, l_name;
EXIT WHEN c_fetchname%notfound;
dbms_output.put_line(l_id || ' ' || l_name );
END LOOP;
CLOSE c_fetchname;
END;
/
1 RAM
2 sham
3 POOJA
OERR error Output
oerr ora 01422
01422, 00000, “exact fetch returns more than requested number of rows”
// *Cause: The number specified in exact fetch is less than the rows returned.
// *Action: Rewrite the query or change number of rows requested
Smart way of Technology 
Pingback: Blackboard Ora-01422: Exact Fetch Returns More Than Requested Number Of Rows
Pingback: Blackboard Ora-01422: Exact Fetch Returns More Than Requested Number Of Rows - BlackboardHub.Com