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

3 thoughts on “insert/update/delete/merge LOG ERRORS”

  1. Michael Moore

    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

  2. Julio Cesar Correa

    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

  3. Julio Cesar Correa

    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

Comments are closed.