Can a rowid be invalidated right after being inserted in Oracle?

Question

I'm running queries that look something like this:

INSERT INTO foo (...) VALUES (...) RETURNING ROWID INTO :bind_var

SELECT ... FROM foo WHERE ROWID = :bind_var

Essentially, I'm inserting a row and getting its ROWID, then doing a select against that ROWID to get data back from that record. Very occasionally though, the ROWID won't be found.

Ignoring the fact that there's probably a better way to do what I'm trying to do, is it possible for a ROWID to change that quickly assuming that there's no one else using the database?

UPDATE There is a trigger involved. Here's the DDL statement for it:

CREATE OR REPLACE TRIGGER "LOG_ELIG_DEMOGRAPHICS_TRG" 
before insert on log_elig_demographics
for each row
begin
select log_elig_demographics_seq.nextval into :new.log_idn from dual;
end;

Essentially, it's just a trigger that is set up to help us emulate an IDENTITY/AUTO INCREMENT field. Is there something wrong with this trigger?

Answer 1

Is there a trigger on the table that might be reversing the insert?

Answer 2

In my experience, the most likely reason for such an error to happen is that somewhere in between, a rollback has happened. Or, if there has been a commit, another user might have deleted the record.

Answer 3

Your INSERT should be:

INSERT INTO foo 
  (primary_key,
   ...) 
VALUES 
  (log_elig_demographics_seq.nextval,
   ...) 
RETURNING primary_key INTO :bind_var

There's no need for the trigger.

Answer 4

Don't do this. It's bad data management.

Answer 5

Hi Jason,

A ROWID won't change unless:

• you move the table (ALTER TABLE t MOVE), from one tablespace to another for example
• the row switches from one partition to another (partitioned table with ENABLE ROW MOVEMENT)
• you update the primary key of an INDEX ORGANIZED table.

When a row moves from one block to another in a standard (HEAP) table, because it grows so large it can't fit into its original block for example, it will be migrated. Oracle will leave a pointer to the new block and move the row. The row will keep its original ROWID.

ROWIDs can be relied upon, they are used in replication to refresh materialized views for example.

Answer 6

I agree with Walter.

Instead of

INSERT INTO foo (...) VALUES (...) RETURNING ROWID INTO :bind_var
SELECT ... FROM foo WHERE ROWID = :bind_var

...why not do the following?

SELECT primaryKey_seq.nextVal
INTO bind_var
FROM dual;

INSERT INTO foo (primaryKeyColumn,...) 
VALUES (bind_var,...);

SELECT ... FROM foo WHERE primaryKeyColumn = bind_var;

Answer 7

A couple of other things may be happening. Firstly, the INSERT may be failing. Are you checking for errors/exceptions ? If not, maybe the value in the variable is junk.

Secondly, you could be inserting something that you can select. Virtual Private Database / Row Level Security could be responsible.

Thirdly, if you commit in between the insert and select, a deferred constraint may force a rollback of the insert.

Fourthly, maybe you are doing a rollback.

Answer 8

How is the bind variable declared? In SQLPlus, you can't use a ROWID type, so there is type conversion going on. I wonder if it's possible that this is munging the ROWID value some of the time.