I got the error below recently and it’s quite new to me. One of the sites I usually visit for more details states that it’s an Oracle bug. But I am sure that the problem is on the script I was running and as explained in the site, the bug is more on the Oracle not displaying the correct problem. That makes this problem more difficult to trace.
ORA-01422: exact fetch returns more than one requested number of rows
Luckily, the code is well commented and there only few updates on it which the version control can easily show the differences between versions. And after careful analysis, the problem is traced on one of the new codes.
For this case, one query inside the cursor loop returned multiple rows. This problem isn’t trapped so the script failed. But instead of throwing ‘too-many-rows’, Oracle threw ora-01422. But anyway, it was a quick fix.
Now, this error apparently appear if you are using functions or queries within a cursor loop like this.
CURSOR c_1 IS
SELECT emp_id, emp_name, dept_id
FOR c IN c_1
WHERE dept_id = c.dept_id;
EXCEPTION WHEN NO_DATA_FOUND THEN
v_dept := NULL;
In case the query on department do NOT return any record, it will be handled by the NO_DATA_FOUND rule. However if there are more than one records, it won’t be handled. That’s where the problem comes in. As I have said earlier, oracle will throw the ora-04122 instead of the TOO-MANY-ROWS.
There’s more explanation about this problem in the link below.