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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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;
RCorfmanCommented:
I believe it is possible to do this as a straight sql statement intead of a pl/sql loop if you are interested...
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...
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

rwkuhnleAuthor Commented:
I would be VERY interested in a straight SQL method...  Anything that can make this process easier would be GREAT!!!
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>

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RCorfmanCommented:
Let me know if that isn't enought to get you going.
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!!!
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.