ORA-00001: violation of unique constraints

 

How to identify a violation of the only key sub-similar SQLERRM

There is a process for updating the user information table every day
We originally used MERGE, but some people think it is slow. We recommend using delete insert.
Delete userinfo wehre logtime> trunc (sysdate)-15; commit;
Insert into userinfo
Select * from t_cache_userinfo a, t_cache_usertype B, t_cache_expuserinfo c
Where a. ui_usertype = B. ut_id
And a. ui_username = c. ui_username
And c. ui_lasttime> trunc (sysdate)-15;

ORA-00001: violation of unique constraints

Oracle SQL % count sqlcode sqlerrm get error and cursor Information

Which row of data is better than ROWID to obtain the keyword that has been violated?

 

 

The failure causes the whole insert to roll back, regardless of howmany rows were inserted successfully. Adding the DML error loggingclause allows us tocomplete the insert of the valid rows.

Insert into dest
SELECT *
FROM source
Log errors into err $ _ dest ('insert') reject limit unlimited;

99998 rows created.
SQL>
The rows that failed during the insert are stored in theERR $ _ DEST table, along with the reason for the failure.

COLUMN ora_err_mesg $ FORMAT A70
SELECT ora_err_number $, ora_err_mesg $
FROM err $ _ dest
WHERE ora_err_tag $ = 'insert ';
ORA_ERR_NUMBER $ ORA_ERR_MESG $
------------------------------------------------------------------------
1400 ORA-01400: cannot insert NULL into ("TEST". "DEST". "CODE ")
1400 ORA-01400: cannot insert NULL into ("TEST". "DEST". "CODE ")

New oracle10g function, log error Experiment

When you use DML statements to perform DML operations on a table, if an error record that violates certain constraints occurs, the entire transaction is aborted and rolled back, this will seriously affect data refresh and waste system performance.
To solve this problem, oracle10g provides an error log table function. With the insert operation example, you can use this function to insert records that meet the constraints in a source record set into the target table,
Inserting records that violate the constraints into the error log table not only makes full use of system resources, but also delays error handling. This is a very useful method for data warehouses.

This method generally involves three steps:
1. Create an error log table in two ways
Method 1:
Use the DBMS_ERRLOG.create_error_log function;
EXEC DBMS_ERRLOG.create_error_log (dml_table_name => 'A ')
The error log table named err $ _ a is created. That is, the error table created starts with err $ _. With the table name added, you can also specify the error log table name.
DBMS_ERRLOG.create_error_log (dml_table_name => 'A', err_log_table_name => 'A _ log ');
Method 2:
Create an error log table manually
However, you must note that the structure of the manually created error log table must meet the requirements of oracle. Otherwise, an error is reported.
2
Execute the DML statement with the error logging parameter. The insert syntax structure is as follows, but I have not found the update or delete statement.
Related syntax
Insert_into_clause {values_clause [returning_clause] | subquery}
[Error_logging_clause]

3
Query the error log table, which is the same as a general select statement.
Next I will use the insert operation as a test case.

-- Create a test table
Create table a
Select rownum id, rownum + 1 VALUE
FROM all_objects
WHERE rownum <3;

 

-- Create a unique index
Create unique index unq_a ON a (id );

-- Use the system package to create the corresponding error log table

EXEC DBMS_ERRLOG.create_error_log (dml_table_name => 'A', err_log_table_name => 'A _ log ');

PL/SQL procedure successfully completed.

SQL> desc A_LOG
Name Null? Type
----------------------------------------------
ORA_ERR_NUMBER $ NUMBER -- NUMBER of error records
ORA_ERR_MESG $ VARCHAR2 (2000) -- error message
ORA_ERR_ROWID $ ROWID -- The rowid of the error record (only for update or delete)
ORA_ERR_OPTYP $ VARCHAR2 (2) -- operation type (I-INSERT, U-UPDATE, D-DELETE)
ORA_ERR_TAG $ VARCHAR2 (2000) -- custom flag description
ID VARCHAR2 (4000) -- original table Field
VALUE VARCHAR2 (4000) -- original table Field

The first five fields are fixed, and the following fields are determined based on the original table.
Note: The Field Types of the original table and the corresponding error log table do not exactly match,

The conversion rules are as follows:

:

NUMBER VARCHAR2 (4000)

CHAR/VARCHAR2 (n) VARCHAR2 (4000)

NCHAR/NVARCHAR2 (n) NVARCHAR2 (4000)

DATE/TIMESTAMP VARCHAR2 (4000)

RAW (2000)

ROWID UROWID

LONG/LOB

 

Insert data. Records with rownum <3 violate the constraints. Records with ROWNUM = 3 are inserted normally.

SQL> INSERT INTO

2 select rownum id, rownum + 1 VALUE

3 FROM all_objects

4 where rownum <4

5 log errors into a_log reject limit Unlimited

6;

1 rows created.
--

Note: The value is unlimited, which means no limit on the number of error records. The default value is 0. If there is an error record, the entire transaction is stopped and rolled back. We can also set a number,
-- If it is 20, that is, when the number of error records exceeds 20, the entire transaction is stopped and rolled back.