ORA-01410 on index organized table
On an Oracle RAC system (11.2.0.1.0)
I have an index organized table:
Code:
CREATE TABLE ROLLUP_NWS_RAINFALL
(
TIMESTAMP DATE,
X INTEGER,
Y INTEGER,
VAL BINARY_DOUBLE,
CONSTRAINT ROLLUP_NWS_RAINFALL$PK1
PRIMARY KEY
(TIMESTAMP, X, Y)
)
ORGANIZATION INDEX
LOGGING
TABLESPACE MAAP_DATA02
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 16M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
COMPRESS 1
PCTUSED 0
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOLOGGING
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
MONITORING;
I populate this table with this PL/SQL block:
Code:
declare
x rollup_nws_rainfall_pkg.coordinateArrayType;
y rollup_nws_rainfall_pkg.coordinateArrayType;
val rollup_nws_rainfall_pkg.valArrayType;
i integer;
begin
DBMS_OUTPUT.ENABLE(1000000);
for i in 1..152
loop
x(i) := i;
y(i) := 0;
val(i) := 0;
end loop;
rollup_nws_rainfall_pkg.INSERTORUPDATERECORDSCNT(:sysdate, x, y, val, 100);
end;
It works as intended when I try to insert 151 records, but I get this error when I try to insert 152 (see for-loop)
ORA-01410: invalid ROWID - line 49 (line with the cursor fetch statement)
All records with matching timestamp are deleted first when more than 151 x,y,val records are inserted, when they should just be updated if different.
Is there some limitation with PL/SQL tables that I can adjust, or some alternate collection type I can use to get around ORA-01410?
Thanks
PL/SQL block depends on the following types & packages:
Code:
CREATE OR REPLACE
TYPE MAAP.ROLLUP_NWS_RAINFALL_REC AS OBJECT
(
TIMESTAMP DATE,
X INTEGER,
Y INTEGER,
VAL BINARY_DOUBLE
)
/
CREATE OR REPLACE
TYPE MAAP.ROLLUP_NWS_RAINFALL_TABLE AS table of ROLLUP_NWS_RAINFALL_REC
/
CREATE OR REPLACE package MAAP.ROLLUP_NWS_RAINFALL_PKG as
type coordinateArrayType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
type valArrayType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
function MakeTableFromArrays(timestamp DATE, xArr coordinateArrayType, yArr coordinateArrayType, valArr valArrayType) return ROLLUP_NWS_RAINFALL_TABLE;
procedure InsertOrUpdateRecordsCnt(argTimeStamp in DATE, argX in coordinateArrayType, argY in coordinateArrayType, argVals in valArrayType, argCount in NUMBER);
end ROLLUP_NWS_RAINFALL_PKG;
CREATE OR REPLACE package body MAAP.ROLLUP_NWS_RAINFALL_PKG as
function MakeTableFromArrays(timestamp DATE, xArr coordinateArrayType, yArr coordinateArrayType, valArr valArrayType) return ROLLUP_NWS_RAINFALL_TABLE
IS
ttable ROLLUP_NWS_RAINFALL_TABLE;
curRecIdx binary_integer;
i binary_integer;
BEGIN
i := 1;
ttable := ROLLUP_NWS_RAINFALL_TABLE();
ttable.extend(xArr.count);
curRecIdx := xArr.first;
while curRecIdx IS NOT NULL
LOOP
ttable(i) := ROLLUP_NWS_RAINFALL_REC(timestamp, xArr(curRecIdx), yArr(curRecIdx), valArr(curRecIdx));
curRecIdx := xArr.next(curRecIdx);
i := i + 1;
END LOOP;
RETURN ttable;
END MakeTableFromArrays;
procedure InsertOrUpdateRecordsCnt(argTimeStamp in DATE, argX in coordinateArrayType, argY in coordinateArrayType, argVals in valArrayType, argCount in NUMBER)
is
ttable ROLLUP_NWS_RAINFALL_TABLE;
ttablecnt integer;
rtablecnt integer;
deletecnt integer;
ri varchar2(128);
ts date;
x integer;
y integer;
val binary_double;
cursor c is
SELECT rowidtochar(r.rowid), r.timestamp, r.x, r.y, r.val FROM
(SELECT timestamp, x, y FROM TABLE (ttable) group by timestamp, x, y) t INNER JOIN
ROLLUP_NWS_RAINFALL r ON (t.timestamp = r.timestamp AND t.x = r.x AND t.y = r.y);
begin
ttable := MakeTableFromArrays(argTimeStamp, argX, argY, argVals);
SELECT COUNT(*) INTO ttablecnt FROM (SELECT timestamp, x, y FROM TABLE (ttable) group by timestamp, x, y);
SELECT COUNT(*) INTO rtablecnt FROM ROLLUP_NWS_RAINFALL WHERE TIMESTAMP = argTimestamp;
SELECT COUNT(*) INTO deletecnt FROM ROLLUP_NWS_RAINFALL N WHERE TIMESTAMP = argTimestamp AND NOT (N.ROWID IN (SELECT R.ROWID FROM
(SELECT timestamp, x, y FROM TABLE (ttable) group by timestamp, x, y) t INNER JOIN ROLLUP_NWS_RAINFALL r ON (t.timestamp = r.timestamp AND t.x = r.x AND t.y = r.y)));
DBMS_OUTPUT.PUT_LINE('ttablecnt = ' || ttablecnt || ' rtablecnt = ' || rtablecnt || ' deletecnt = ' || deletecnt);
open c;
LOOP
fetch c into ri, ts, x, y, val;
exit when c%notfound;
DBMS_OUTPUT.PUT_LINE('ri '|| ri || ' timestamp ' || ts || ' x ' || x || ' y ' || y || ' val ' || val);
END LOOP;
DELETE ROLLUP_NWS_RAINFALL WHERE TIMESTAMP = argTimestamp AND ROWID NOT IN (SELECT R.ROWID FROM
(SELECT timestamp, x, y FROM TABLE (ttable) group by timestamp, x, y) t INNER JOIN ROLLUP_NWS_RAINFALL r ON (t.timestamp = r.timestamp AND t.x = r.x AND t.y = r.y));
IF argCount > 0 THEN
MERGE INTO ROLLUP_NWS_RAINFALL r USING
(SELECT timestamp, x, y, max(val) val FROM TABLE (ttable) group by timestamp, x, y) ri
ON (r.timestamp = ri.timestamp AND r.x = ri.x AND r.y = ri.y)
WHEN MATCHED THEN UPDATE SET
r.val = ri.val
WHERE r.val <> ri.val
WHEN NOT MATCHED THEN INSERT
(r.timestamp, r.x, r.y, r.val) VALUES (ri.timestamp, ri.x, ri.y, ri.val);
END IF;
end InsertOrUpdateRecordsCnt;
END ROLLUP_NWS_RAINFALL_PKG;
/