Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Posts
    3

    Unanswered: Help: Cursor is not exiting

    CORRECTION: it is my loop that will not exit, i am desperate PLZ HELP!
    I have a problem. , and I am hoping to get some help.
    I want to output data x, based on n # of records.

    I wrote this loop:
    LOOP
    FETCH myCursor INTO x;
    EXIT WHEN (myCursor%ROWCOUNT >= (n+1));
    DBMS_OUTPUT.PUT_LINE(x) ;
    END LOOP;

    This works fine until (n >= “# of records”).
    For example if I have the records: A B C D
    When n =3, return A B C
    When n >=4, return A B C D D D D D D…., which results in overflow error.

    Please help me understand why myCursor will not exit.

    Thank you in advance,
    The seeker
    Last edited by SeekingTheOracle; 01-25-09 at 13:51.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post DDL for table
    post DML for sample/test data
    post complete PL/SQL procedure
    post CUT & PASTE of whole session.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2009
    Posts
    3
    thanks anyway, but i cannot post all me code,

    I decided that %rowcount was the problem.

    so i decided to replace
    EXIT WHEN (myCursor%ROWCOUNT >= (n+1));

    with

    EXIT WHEN (aNumber >= n);
    aNumber := (aNumber + 1);

    That prevents my overflow problem.
    It does however, repeat the last record until aNumber >= n, when n > #of records,
    but I feel that is better than overflow.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by SeekingTheOracle
    I decided that %rowcount was the problem.
    I see here a little different problem - you do not check %NOTFOUND at all. So when no row is fetched, data from the previous row are not overwritten and compared forever.
    Quote Originally Posted by SeekingTheOracle
    but I feel that is better than overflow.
    So, you do not mind to get A B C D D when N=5?
    What about using FOR LOOP (as you know the number of loops)?
    Using EXIT WHEN <cursor_name>%NOTFOUND for not repeating the last row when N >= <number_of_rows_returned_by_cursor>?

  5. #5
    Join Date
    Jan 2009
    Posts
    3
    Quote Originally Posted by flyboy
    So, you do not mind to get A B C D D when N=5?
    What about using FOR LOOP (as you know the number of loops)?
    Using EXIT WHEN <cursor_name>%NOTFOUND for not repeating the last row when N >= <number_of_rows_returned_by_cursor>?
    Thankx flyboy, I am going to try that, this is school work and i am new to pl/sql, that is why i can not post my code. I am not looking for others to do my work for me, only to hopefully be pointed in the right direction after I have exhausted my arsenal, which at this point is very limited.

    Also, it does not make me extremely happy to get A B C D D @ n=5, but it was definitely better than the overflow, which was completely unacceptable.
    I will try again with a for loop & myCursor%notfound. Thank you very much.
    Have a super day,
    ~C

    Update: yep myCursor%notfound. did the trick, now when n=100 program returns A B C D,
    however, I am a bit uncertain of the proper syntax of a for loop, i will look it up. thanks again!!
    Last edited by SeekingTheOracle; 01-26-09 at 08:28.

Posting Permissions

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