CODEWITHSUNDEEP
oracle-databaseplsqldatastage
Lyrk
Lyrk

Reputation: 2000

ORA-01410: invalid ROWID

When I am trying to fetch updates on source table with below code, I am getting

**Error code: 1,410, Error message: ORA-01410: invalid ROWID
ORA-06512: at "DS2ODS_DW_PRODUCT", line 43**

error and updated rows can not be mirrored to target. (NOTE: I succeeded INSERTS) Line 43 refers to UPDATE DW PRODUCT... line. Do you see any mistake?

NOTE: I am trying to apply source table updates to target table by using below code.

CREATE OR REPLACE PROCEDURE DS2ODS_DW_PRODUCT(
DM_OPERATION_TYPE       IN  char,
BEFORE_PRODUCT_ID       IN  NUMBER,
BEFORE_DESCRIPTION      IN  CHAR,
BEFORE_PRICE            IN  NUMBER,
AFTER_PRODUCT_ID        IN  NUMBER,
AFTER_DESCRIPTION       IN  CHAR,
AFTER_PRICE             IN  NUMBER
)
IS
dummy number;
BEGIN
CASE DM_OPERATION_TYPE
WHEN 'I' THEN
INSERT INTO DW_PRODUCT (
PRODUCT_ID,
DESCRIPTION,
PRICE)
VALUES
(AFTER_PRODUCT_ID,
AFTER_DESCRIPTION,
AFTER_PRICE);
WHEN 'D' THEN 
DECLARE CURSOR CURSOR_FOR_DELETE  is SELECT 1 
FROM DW_PRODUCT
WHERE PRODUCT_ID=BEFORE_PRODUCT_ID AND DESCRIPTION=BEFORE_DESCRIPTION AND 
PRICE=BEFORE_PRICE FOR UPDATE;
    BEGIN
    OPEN CURSOR_FOR_DELETE;
    loop 
    FETCH  CURSOR_FOR_DELETE INTO dummy;
      DELETE FROM DW_PRODUCT WHERE CURRENT OF CURSOR_FOR_DELETE;
   end loop;
    CLOSE CURSOR_FOR_DELETE;
  END;
WHEN 'U' THEN
DECLARE CURSOR CURSOR_FOR_UPDATE  is SELECT 1 
FROM DW_PRODUCT
WHERE PRODUCT_ID=BEFORE_PRODUCT_ID AND DESCRIPTION=BEFORE_DESCRIPTION AND 
PRICE=BEFORE_PRICE FOR UPDATE;
    BEGIN
    OPEN CURSOR_FOR_UPDATE;
    loop
    FETCH CURSOR_FOR_UPDATE INTO dummy;
      UPDATE DW_PRODUCT SET PRODUCT_ID=AFTER_PRODUCT_ID,DESCRIPTION=AFTER_DESCRIPTION,PRICE=AFTER_PRICE
      WHERE CURRENT OF CURSOR_FOR_UPDATE;
     end  loop;
    CLOSE CURSOR_FOR_UPDATE;
  END;
END CASE;
END;
/

Upvotes: 1

Views: 6523

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59486

If this is your real code then make it simply like this:

CREATE OR REPLACE PROCEDURE DS2ODS_DW_PRODUCT(
DM_OPERATION_TYPE       IN  char,
BEFORE_PRODUCT_ID       IN  NUMBER,
BEFORE_DESCRIPTION      IN  CHAR,
BEFORE_PRICE            IN  NUMBER,
AFTER_PRODUCT_ID        IN  NUMBER,
AFTER_DESCRIPTION       IN  CHAR,
AFTER_PRICE             IN  NUMBER
)
IS

BEGIN
CASE DM_OPERATION_TYPE
WHEN 'I' THEN
   INSERT INTO DW_PRODUCT (PRODUCT_ID,DESCRIPTION,PRICE)
   VALUES (AFTER_PRODUCT_ID,AFTER_DESCRIPTION,AFTER_PRICE);
WHEN 'D' THEN 
   DELETE FROM DW_PRODUCT 
   WHERE PRODUCT_ID=BEFORE_PRODUCT_ID 
      AND DESCRIPTION=BEFORE_DESCRIPTION 
      AND PRICE=BEFORE_PRICE;
WHEN 'U' THEN
   UPDATE DW_PRODUCT SET  
      PRODUCT_ID=AFTER_PRODUCT_ID, 
      DESCRIPTION=AFTER_DESCRIPTION, 
      PRICE=AFTER_PRICE
   WHERE PRODUCT_ID=BEFORE_PRODUCT_ID 
      AND DESCRIPTION=BEFORE_DESCRIPTION 
      AND PRICE=BEFORE_PRICE;
END CASE;
END;
/

Upvotes: 1

Related Questions