insert/update/delete/merge LOG ERRORS
May 15th, 2007
the following works only on 10gR2
Table PRODUCTCATEGORY:
| CATEGORY (PK) |
|---|
| Fruit |
| Fish |
Table TMP_PRODUCT:
| PRODUCT | CATEGORY |
|---|---|
| Banana | Fruit |
| Chicken | Meat |
Table PRODUCT:
| PRODUCT (PK) | CATEGORY (FK) |
|---|
Now I want to load product
SQL> insert into product select * from tmp_product;
insert into product select * from tmp_product
*
ERROR at line 1:
ORA-02291: integrity constraint (AUDBA.SYS_C0016368)
violated - parent key not found
Well, there is a missing category. I will fix it later. I want to load the valid data now.
SQL> exec dbms_errlog.create_error_log('PRODUCT')
PL/SQL procedure successfully completed.
SQL> insert into product select * from tmp_product
2 log errors reject limit unlimited;
1 row created.
Table PRODUCT:
| PRODUCT (PK) | CATEGORY (FK) |
|---|---|
| Banana | Fruit |
Now I have successfully loaded my valid data. I need to fix the problem. Let’s identify the offending row :
SQL> select ORA_ERR_OPTYP$,ORA_ERR_MESG$,CATEGORY
2 from err$_product;
ORA_ERR_OPTYP$ ORA_ERR_MESG$ CATEGORY
-------------- -------------------------------- --------
I ORA-02291: integrity constraint Meat
(AUDBA.SYS_C0016368) violated -
parent key not found
1 row selected.
Let’s create the missing category manually
SQL> insert into productcategory(category) values ('Meat');
1 row created.
SQL> insert into product
2 select * from tmp_product
3 where category='Meat';
1 row created.
Table PRODUCTCATEGORY:
| CATEGORY (PK) |
|---|
| Fruit |
| Fish |
| Meat |
Table PRODUCT:
| PRODUCT (PK) | CATEGORY (FK) |
|---|---|
| Banana | Fruit |
| Chicken | Meat |
Laurent,
This does not apply to your blog subject but I didn’t know how else to contact you. Please feel free to delete this.
As a comment on my own blog, you had referred me to Expression Filters. I have been doing some testing. I think I found a bug.
Given that the expression looks like this:
(Attribute03 in ( ‘hi’)) AND Attribute08 >= 75000
GET_EXPRSET_STATS will fail to find Attribute08. Removal of the leading parenthesis will cause it to work correctly.
DBMS_EXPFIL.GET_EXPRSET_STATS (expr_tab => ‘criteria4′,
expr_col => ‘match_criteria’);
SELECT *
FROM user_expfil_exprset_stats;
I was wondering if you could confirm this.
Thanks,
Mike
Hi Laurent,
I’m trying to do the same thing that you did but using merge clause.My db version is 11.2.The details are:
-the table merged(target db) is on another database(i’m acessing by dblink from source db)
- the error log table(created by dbms_errlog) is on the source db
When I run the MERGE I got a error :
ERRO na linha 1:
ORA-00001: restric?o exclusiva (T_TRANSACTIONS.PK_TRANSACTION) violada
ORA-02063: precedendo line a partir de dbtarget
And on the error log table nothing is logged!
I found some discussions on AskTom but nothing to help me yet.
Thanks,
Julio Cesar
Sao Paulo,Brazil
Now I found :
The following conditions cause the statement to fail and roll back without invoking the error logging capability:
Violated deferred constraints
Out-of-space errors
Any direct-path INSERT operation (INSERT or MERGE) that raises a unique constraint or index violation
Any UPDATE operation (UPDATE or MERGE) that raises a unique constraint or index violation