I am dealing with a 3rd party legacy application, in which the code cannot be changed.
Periodically, this application inserts rows in a table which causes it to have a Primary key error.
I need a way to "stop Oracle" from raising a exception of "ORA-00001: unique constraint" when bad rows get inserted.
At first I tried to use a "before insert" trigger, but this got me with messages about mutating table. Ugh.
I did some research and found a couple of techniques would could help me, but they both required a change
to the insert statement.
Those techniques are using:
This require that the insert statement is modified to include the following statement. Assume I have a table called T
log errors into err$_t ('trap_pk')
reject limit unlimited
Another approach I thought of is to use the "hint" of /*+ ignore_row_on_dupkey_index(t, t_pk) */
Again, I would need to modify the statement!
So, how can I achieve this?
Stored outlines? The statement that fire doesn't use bind variables, so not sure If I can go this way.
Sql Plan Management?
I am not sure 100% If I can do what I need to do...
Here is my example to play with....
create table t ( x int );
alter table t add constraint t_pk primary key(x);
insert into t values ( 1);
insert into t values ( 1); << This would give me the primary key exception
insert /*+ ignore_row_on_dupkey_index(t, t_pk) */ into t values ( 1); << THIS DOES NOT give me error, just silently "ignores" it! I want this
to happen dynamically without having to change the code.
So, how can I "inject" this "hint" into the query? What approach and can you share any code?
Thanks again for any advice.
I think you might be out of luck here. All of the facilities for injecting hints are typically for *reading* data (even if those reads are part of an update etc). To quote the docs from (for example) outlines:
"You can specify any one of the following statements: SELECT, DELETE, UPDATE, INSERT-SELECT, CREATE TABLE AS SELECT."
Similarly, looking at DBMS_ADVANCED_REWRITE, it doesnt appear to support binds.
There is DBMS_SQL_TRANSLATOR, but that's a 12c feature.
I must admit, this strikes me as not the best way to tackle the problem, because of the risks (ie, you'd never know if you'd rejected 1 rows or 10 million rows over the lifetime of the table).
The only thing I could think of would be replacing all the tables with views of the same name, and using instead-of triggers, but that seems a drastic approach.