Monday, April 30, 2007

Oracle DML Error Logging and ORA-38908

Have you ever encountered an error like this?

ORA-38908: internal error occurred during DML Error Logging ORA-00942: table or view does not exist ORA-02291: integrity constraint (T1.T1_FK) violated - parent key not found

You are probably using DML Error Logging, a statement like this

insert into my_tab (c1, c2, c3)
select a.c1, a.c2, a.c3 from my_other_tab a
LOG ERRORS INTO err$_my_tab ('oops') REJECT LIMIT UNLIMITED;

The reason is that err$_my_tab (and probably my_tab) is owned by a user A but the statement is being run by user B and user B does not have insert on err$_my_tab. It's fairly clear from the documenation that this is required.

Security

The user who issues the INSERT statement with DML error logging must have INSERT privileges on the error logging table.


The problem is, this is the case even if the insert statement is part of a package (my_pkg) and user B has execute on my_pkg. It seems that user B should not need insert on err$_my_tab under this scenario, but it does. This is probably because the DML Error Logging uses autonomous transactions (I assume) and operates outside the context of the package. In this light, it makes sense.

1 comment:

Anonymous said...

I have a table that has a self reference relationship and when I update the table it throws the ORA-02291: integrity constraint violation.

I am using DML error logging table to capture Insert/update errors but with the above error it does not capture errors to table. Is this the expected behavior ? is it whats been documented as "Any UPDATE operation (UPDATE or MERGE) that raises a unique constraint or
index violation." , to me doesnt !since its not a unique constraint or index violation (directly). so if you have any ideas please let me know.