ORA-01410: invalid ROWID error

From: cschang <cschang_at_maxinter.net>
Date: Wed, 31 Mar 2004 22:42:57 -0500
Message-ID: <406B8FC1.9010709@maxinter.net>

I wrote a test procedure using the "FOR UPDATE" and the "WHERE CURRENT OF ..", but I kept getting the error of INVALID ROWID. Does it mean it can not be used with the BULK COLLECT INTO. In fetching. As long as I remove the WHERE CURRENT OF, and use the WHERE ROWID = v_rowid(I), it is OK. Even I did not include the rowid in the cursor's SELECT statement, by just ID, I would still get the error. I have searched in the documentation and on-line, can not find any clue why. Any explaination? Appreciate.

For the book, using the rowid is better performance, According to books, useing the "WHERE CURRENT OF .." is accompanied with the "FOR UPDATE" in cursor so that the update statement does not have to use the index search again. I just want to try the "WHERE CURRENT OF .." syntax. My system is 8.1.7 on NT 4

CREATE OR REPLACE PROCEDURE TestForUpdate(p_siteID IN po.site_id%TYPE ) AS

    TYPE t_ID IS TABLE OF po.ID%TYPE;     TYPE t_rowID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;  

    v_ID          t_ID;
    v_rowID         t_rowID;

    CURSOR c_SX_PO IS
        SELECT rowid, ID
        FROM po
        WHERE vendor_id = 'SX'
        AND po_status = 'NEW'
        AND site_id = p_siteID
        FOR UPDATE;
       

 BEGIN     OPEN c_SX_PO;
    FETCH c_SX_PO BULK COLLECT INTO v_rowID, v_ID;    

    FOR i IN v_rowID.FIRST .. v_rowID.LAST     LOOP

        UPDATE po
        SET po_status = 'EX',
            execute_date = sysdate,
            execute_buyer = USER
        WHERE CURRENT OF  c_SX_PO;
        -- rowid = v_rowID(i);  or id = v_ID(i);

    END LOOP;
    CLOSE c_SX_PO;  

 EXCEPTION
    WHEN OTHERS THEN

       DECLARE
       v_errText       VARCHAR2(200);
       BEGIN
          v_errText:= SUBSTR(SQLERRM,1,200);   
          DBMS_OUTPUT.PUT_LINE( v_errText);
       END;

END TestForUpdate;
/ Received on Wed Mar 31 2004 - 21:42:57 CST