?
Solved

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

Posted on 2006-04-15
8
Medium Priority
?
4,184 Views
Last Modified: 2013-12-12
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
0
Comment
Question by:rwkuhnle
  • 5
  • 3
8 Comments
 

Author Comment

by:rwkuhnle
ID: 16461104
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16462191
I believe it is possible to do this as a straight sql statement intead of a pl/sql loop if you are interested...
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16462196
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rwkuhnle
ID: 16462247
I would be VERY interested in a straight SQL method...  Anything that can make this process easier would be GREAT!!!
0
 
LVL 16

Accepted Solution

by:
RCorfman earned 2000 total points
ID: 16462295
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16462311
Let me know if that isn't enought to get you going.
0
 

Author Comment

by:rwkuhnle
ID: 16462335
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16462341
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question