insert/update/delete/merge LOG ERRORS
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 |
May 17th, 2007 at 18:01
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