PL/SQL too_many_rows condition

[welchdor]

I am processing cursor rows in a FOR loop. I do a "SELECT INTO" from another table to see if I find a match on one of my cursor record items. If I find no matches, I do further processing (WHEN NO_DATA_FOUND THEN....); I may also find MORE than one match (TOO_MANY_ROWS). If I do, or if I find ONE matching row, I need to do further processing on either the one or all of the other ones. My question is, is TOO_MANY_ROWS actually an error and it doesn't return all the rows, or can I use it to process all of them? If it doesn't return them all, then do I need another cursor to handle the possible multiples? If so, when I declare the 2nd cursor will I have problems if the WHERE clause references
an item from the first cursor?
Any help is appreciated.

[chrisrlong]

Just a general statement first - cursor processing should be avoided if and when the job can be done in straight SQL. Having said that...

Yes, TOO_MANY_ROWS is an actual error. You are trying to SELECT multiple values INTO a single variable. This is not possible, and you cannot use the SELECT INTO to 'process' all these target rows as you suggest.

The solution is to use another cursor as you specified. Your first cursor is fetching each row into a variable or set of variables. You then open the second cursor with these variables as params and roll through any possible result set. No need for any SELECT INTO.

HTH,

- Chris

[6502]

Easy. You can define a cursor which you can pass parameters on runtime.
------------cut here ---------------
CURSOR c_second(p_value) IS
SELECT value1
FROM table2
WHERE column1 = p_value;
------------cut here ---------------
------------cut here ---------------
FOR r_second IN c_second(value_from_first_cursor) LOOP
--process
END LOOP;
------------cut here ---------------

Commit;
6502