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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.