Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2002
    Posts
    20

    Unanswered: Cursor loop unable to use Sequence NextVal

    Hi,

    I am looping through a cursor and then wish to assign the next_id to a variable before doing an update, at each iteration of the loop.

    Unfortunately Oracle 9i is saying this is not allowed in this context. What can I do to get around this problem?

    This is my code

    procedure myProc is
    cursor myCursor is
    select myVarA, myVarB from myTbl;
    next_id number;
    begin
    for l_row in myCursor loop
    next_id := mySequence.NEXTVAL;

    update myTbl2
    set a = l_row.myFldA,
    b = l_row.myFldB;

    commit;
    end loop;
    end myProc;

  2. #2
    Join Date
    Mar 2002
    Posts
    20
    sorry my code reads

    procedure myProc is
    cursor myCursor is
    select myFldA, myFldB from myTbl;
    next_id number;

    begin
    for l_row in myCursor loop
    next_id := mySequence.NEXTVAL;

    update myTbl2
    set a = l_row.myFldA,
    b = l_row.myFldB;

    commit;
    end loop;

    end myProc;

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, you could do this:

    ...
    for l_row in myCursor loop
    select mySequence.NEXTVAL into next_id from dual;
    ...

    But since you never actually use next_id, this seems a little pointless. If you meant to use next_id in the update, then you can avoid the select like this:

    for l_row in myCursor loop

    update myTbl2
    set a = l_row.myFldA,
    b = l_row.myFldB
    c = mySequence.NEXTVAL;
    ...

  4. #4
    Join Date
    Mar 2002
    Posts
    20
    great, thanks

Posting Permissions

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