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.


DECLARE
CURSOR c_1 IS
SELECT emp_id, emp_name, dept_id
FROM employees;

v_dept VARCHAR2(30);
BEGIN
FOR c IN c_1
LOOP
BEGIN
SELECT deptname
INTO v_dept
FROM department
WHERE dept_id = c.dept_id;
EXCEPTION WHEN NO_DATA_FOUND THEN
v_dept := NULL;
END;
..yada...yada.. yada.
...
END LOOP;

END;

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.
http://www.dba-oracle.com/sf_ora_01422_exact_fetch_returns_more_than_one_requested_number_of_rows.htm

Comments are closed.

Post Navigation