Getting "too many rows" error inside a "for" cursor loop

Question

I have a trigger that contains two cursors loops, one nested inside the other like this:

FOR outer_rec IN outer_cursor
LOOP
  FOR inner_rec IN inner_cursor
  LOOP
     -- Do some calculations
  END LOOP;

END LOOP;

Somewhere in this it is throwing the following error:

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

I've been trying to determine where it's coming from for an hour or so.. but should this error never happen?

Also.. I am assuming the inner loop automatically closes and opens itself again every time the outer loop goes the next record, i hope this is correct.

Answer 1

From what I know, ORA-01422 gets triggered only if you have a SELECT...INTO statement in the PL/SQL block, where the select statement fetches more than 1 row. Remember that a SELECT..INTO can fetch only a single row, if more than one row gets fetched, the above exception gets raised.

Answer 2

You can also get the 1422 on an UPDATE statement that does a SELECT for its values.

Are you not getting a stack trace with a line number?

Answer 3

I was calling a stored procedure that had an OUT variable, and somehow when it tried to use the out variable, this error occured.