Re: ORA-01410: invalid ROWID
Date: Wed, 22 Jan 2003 12:50:59 +0000
Message-ID: <2422020.1043239859_at_dbforums.com>
Thanks for responding. We (the DBAs) have checked, all object are
valid. Alert Logs have produced nothing. We've run DBVERIFY, it showed
us no errors. No DB Links. No privileges issues. I'm storing the
ROWID as a ROWID type. We're running oracle 8.1.7.4 on 2000. Like I
said, it's truly weird. This trigger scheme has been working well for
six months and then just started throwing these ORA-01410 errors.
Here is a sample...
The package to hold the ROWID....
CREATE OR REPLACE PACKAGE PAK_MUTATING_STATE
AS
TYPE ridArray IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
END PAK_MUTATING_STATE;
First, a before statement trigger clears out the package var....
CREATE OR REPLACE TRIGGER TR_BEFORE_STMT
BEFORE INSERT OR UPDATE
CREATE OR REPLACE TRIGGER TR_AUDIT_ROW_LEVEL
AFTER INSERT OR UPDATE
newRIDs ridArray;
emptyRID ridArray;
/
ON TABLE
BEGIN
PAK_MUTATING_STATE.newRIDs := PAK_MUTATING_STATE.emptyRID;
END;
Each Row level trigger grabs the ROWID of the inserted / update row...
ON TABLE
FOR EACH ROW
BEGIN
PAK_MUTATING_STATE.newRIDs(PAK_MUTATING_STATE.newRIDs.count + 1)
:= :NEW.rowid;
END;
Lastly, an After statement trigger populates populates the audit table
with every change ....
CREATE OR REPLACE TRIGGER TR_AFTER_STMT
AFTER INSERT OR UPDATE
ON TABLE
BEGIN
for inx in PAK_MUTATING_STATE.newRIDs.FIRST ..
PAK_MUTATING_STATE.newRIDs.LAST
LOOP
BEGIN
INSERT INTO TABLE_AUDIT
select * from TABLE
where ROWID = PAK_MUTATING_STATE.newRIDs(inx);
END;
END LOOP;
END; Also, I should note that these tables are not IOT table nor are they partitioned. (A suggestion from someone else).
-- Posted via http://dbforums.comReceived on Wed Jan 22 2003 - 13:50:59 CET