Clever Geek Handbook

Could rowid be invalid immediately after insert in Oracle?

I am 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 insert a row and get its ROWID and then choose against that ROWID to get the data from that record. However, very rarely, the ROWID will not be found.

Ignoring the fact that there is probably the best way to do what I am trying to do , is it possible for the ROWID to change so quickly, assuming nobody else is using the database?

UPDATE Trigger is on. Here is the DDL instruction 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;

Basically, it's just a trigger that's configured to help us emulate the IDENTITY / AUTO INCREMENT field. Is there something wrong with this trigger?

+2


source to share


7 replies


ROWID will not change if:

  • you are moving a table (ALTER TABLE t MOVE), from one tablespace to another, like
  • row switches from one partition to another (partitioned table with ENABLE ROW MOVEMENT)
  • you are updating the primary key of the INDEX ORGANIZED table.


When a row is moved from one block to another in a standard (HEAP) table because it gets so big that it cannot fit into the original block, for example it will be wrapped. Oracle will leave a pointer to the new block and move the row. The string will retain the original ROWID.

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

+3


source


Your INSERT should be:

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


No trigger needed.

+2


source


I agree with Walter.

Instead

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;
+2


source


A few more things can happen. First, the INSERT can fail. Are you checking for errors / exceptions? If not, the value in the variable is probably trash.

Secondly, you can insert something that you can choose from. Virtual private database / row level security can be a liability.

Third, if you commit between insertion and selection, the deferred constraint can cause the insertion to be rolled back.

Fourth, you may be doing a rollback.

+2


source


Is there a trigger on the table that could flip the insert?

+1


source


In my experience, the most likely reason for this error is that there was a rollback somewhere in the middle. Or, if a commit occurred, another user might have deleted the entry.

+1


source


How is the bind variable declared? In SQLPlus, you cannot use the ROWID type, so there is a type conversion. I am wondering if it is possible that this is dropping the ROWID value for a while.

+1


source






More articles:


All Articles