Re: ORA-01410: invalid ROWID

wiggam14 <member22780_at_dbforums.com> wrote in message news:<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;
>
> newRIDs ridArray;
> emptyRID ridArray;
>
> 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
> ON TABLE
> BEGIN
> PAK_MUTATING_STATE.newRIDs := PAK_MUTATING_STATE.emptyRID;
> END;
>
> Each Row level trigger grabs the ROWID of the inserted / update row...
>
> CREATE OR REPLACE TRIGGER TR_AUDIT_ROW_LEVEL
> AFTER INSERT OR UPDATE
> 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).

Couple of things.
1) Have you tried recompiling all the above mentioned objects? 2) Have you checked if all three triggers are enabled. A disabled trigger is valid and will not show up as invalid.

On a side note... is there a reason why you are going thru a package and three triggers just to replicate a row? This could easily be handled with one after insert or update trigger.

Regards
/Rauf Sarwar Received on Thu Jan 23 2003 - 03:12:12 CET