PL/SQL too_many_rows condition
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: PL/SQL too_many_rows condition

  1. #1
    Join Date
    Jan 2001
    Posts
    63
    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.

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  3. #3
    Join Date
    Mar 2001
    Location
    Cologne, Germany
    Posts
    24
    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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width