ORA-01410: invalid ROWID ... Update - Where Current Of

Hi All,

I'm attempting for the first time to put together a PL/SQL script to sequentially update a field within a table...  I'm working with the following:

DECLARE
    /* Output variables to hold the result of the query: */
      i NUMBER(6) := 90000;
    /* Cursor declaration: */
    CURSOR GM_BOOK_Cursor IS
        SELECT C12_BOOK_ZKEY, C11_BOOK_REFRESH, C14_BOOK_UTIL1
        FROM ZGASMASTER_BOOK_MERGE
        WHERE C12_BOOK_ZKEY=1
        FOR UPDATE;
BEGIN
    OPEN GM_BOOK_Cursor;
    LOOP
            UPDATE ZGASMASTER_BOOK_MERGE SET C12_BOOK_ZKEY = i WHERE CURRENT OF GM_BOOK_Cursor;
            i := i + 1;
    END LOOP;
    /* Free cursor used by the query. */
    CLOSE GM_BOOK_Cursor;
END;

run;

When I attempt to run this I get an ORA-01410 error.  In essence I'm trying to update a set of rows with an updated counter value (i) where each row will increment by i + 1...  Perhaps I'm going down the wrong path altogether???

Thanks in advance.
-Rob
rwkuhnleAsked:
Who is Participating?
 
RCorfmanCommented:
SQL> select c12_book_zkey from zgasmaster_book_merge where c12_book_zkey=1;

C12_BOOK_ZKEY
-------------
            1
            1
            1
            1
            1
            1
            1
            1
            1
            1
            1
            1

12 rows selected.

SQL> update zgasmaster_book_merge set c12_book_zkey=rownum
  2   where c12_book_zkey=1
  3  ;

12 rows updated.

SQL> select c12_book_zkey from zgasmaster_book_merge;

C12_BOOK_ZKEY
-------------
            1
            2
            3
            4
            5
            6
            7
            8
            9
           10
           11
           12

12 rows selected.

SQL>
0
 
rwkuhnleAuthor Commented:
Well, it looks like I figured it out on my own...  Thanks to anyone that may have been looking at it till now...  I used the following to get it to work.

DECLARE
    /* Output variables to hold the result of the query: */
        i NUMBER(6) := 90000;
    /* Cursor declaration: */
       v_row ZGASMASTER_BOOK_MERGE%ROWTYPE;
    CURSOR GM_BOOK_Cursor IS
        SELECT *
        FROM ZGASMASTER_BOOK_MERGE
        WHERE C12_BOOK_ZKEY=4
        FOR UPDATE;
BEGIN
    OPEN GM_BOOK_Cursor;
    LOOP
       fetch GM_BOOK_Cursor into v_row;
       exit when GM_BOOK_Cursor%notfound;
       UPDATE ZGASMASTER_BOOK_MERGE SET C11_BOOK_REFRESH, C12_BOOK_ZKEY, C14_BOOK_UTIL1 = i WHERE CURRENT OF GM_BOOK_Cursor;
       i := i + 1;
    END LOOP;
    /* Free cursor used by the query. */
    CLOSE GM_BOOK_Cursor;
END;
0
 
RCorfmanCommented:
I believe it is possible to do this as a straight sql statement intead of a pl/sql loop if you are interested...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
RCorfmanCommented:
straight sql is much faster/efficient than using a pl/sql cursor loop and should be used when possible for efficiency purposes if it makes sense. I don't know if this is just part of a much larger problem that is more complicated than the example, but if it just this, I think it would be worth looking at the sql option...
0
 
rwkuhnleAuthor Commented:
I would be VERY interested in a straight SQL method...  Anything that can make this process easier would be GREAT!!!
0
 
RCorfmanCommented:
Let me know if that isn't enought to get you going.
0
 
rwkuhnleAuthor Commented:
Thanks!  

It's close but I guess there is more to the problem than I really mentioned; this is what I tried last night trying to make it work...  The value of c12_book_zkey has to be primed with a starting point; in my sample it was 90000...  There are other rows in the table all with unique C12_BOOK_ZKEY values and I need to make certain that I don't duplicate those values...  i.e. the value of max(c12_book_zkey) = 87914 currently.  

If you have a way to fix that....  Hey I guess I could just add the pimer number to rownum....   How dumb of me :-)

Guess this should work then...
update zgasmaster_book_merge set c12_book_zkey=rownum + 87914 where c12_book_zkey=1

Thanks!!!
0
 
RCorfmanCommented:
yep... and that will work.  Glad to help... There are situations where this won't work, but for a straight update like this it does.  Enjoy.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.