CODEWITHSUNDEEP
oracle-databaseinsertsequence
llanato
llanato

Reputation: 2491

Insert Entry and Ignore Sequence

I have a table where a sequence adds the primary id for each record, now record number 3 was deleted and I need to insert it back in with the ID of 3 but because the sequence exists it wont let me, can anyone advise? I get the below message when I try to insert.

ORA-20001: Table sequence numbers are supplied internally; please do not include in insert SQL statements

ORA-06512 at: "owner.trigger_name", line 4

ORA-04088: error during execution of trigger 'owner.trigger_name'

Upvotes: 1

Views: 1274

Answers (1)

Alex Poole
Alex Poole

Reputation: 191315

The ORA-20001 error is in the user-defined range, so it's your own code (or that of the application, anyway) that's complaining - it isn't Oracle itself.

It looks like the trigger that is assigning the ID is doing a sanity check and raising that exception when it sees that a value has been manually provided during the insert. That is to protect you from yourself, presumably, and it will prevent you putting in a value that is higher than the current sequence number - which would cause an error later when the sequence reached the same value. (Assuming it's a primary key, or at least has a unique constraint).

Since it's coming from your own application code, the only way around it is to identify and disable the trigger that's assigning the ID value from the sequence and throwing the exception.

alter trigger <trigger_name> disable;

... and then when you're done:

alter trigger <trigger_name> enable;

But I'd only do this during an outage or when you can at least guarantee that there will be no other inserts on this table. Any inserts while the trigger is disabled will not get an ID assigned. They should error too - again assuming it's a primary key, or a not-null unique key - but that is still not desirable.

If you really can't prevent other inserts, you could (temporarily) recreate the trigger without the sanity check, but even so, that isn't something to do lightly.

Of course, if it's a synthetic key then the actual key value should not matter, in which case it ought to to fine to do a normal insert and get a new ID value for the re-inserted row. If some other table has a reference to 3 then it sounds like you don't have foreign key constraints, which would have prevented that row being deleted in the first place.

Upvotes: 4

Related Questions