ORA-00001: Violating UNIQUE constraints

How to detect a similar sqlerrm against a single key

There is a process to update the user Information table daily
Originally used in the merge to do, but some people think it slow, suggest to use delete insert to do
I first 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: Violating UNIQUE constraints

Oracle has sql%count sqlcode SQLERRM get error and cursor information

To get a violation of the keyword which line of data is best rowid?

The failure causes the whole insert to roll back, regardless the howmany rows were inserted. 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 at 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 ")

oracle10g new function, log error experiment

When you perform a DML operation on a table through a DML statement, if an error record that violates some constraint occurs, the entire transaction is aborted and rolled back, which can severely affect the data refresh and waste system performance
, in order to solve this problem, ORACLE10G provides the function of an error logging table, with an insert operation that can be used to insert a record that satisfies the constraint in a collection of source records into the destination table.
Inserting a record that violates a constraint into an error log table is a useful way to take advantage of the resources of the system and to defer the processing of error records for the Data Warehouse.

The specific use of this method typically consists of three steps:
1 Create error Log table, there are two kinds of methods
Law I:
Using Dbms_errlog.create_error_log function;
EXEC dbms_errlog.create_error_log (dml_table_name => ' A ')
Create the error log table named Err$_a, that is, create an error table beginning with err$_, plus the table name, we can also specify the error log table name
Dbms_errlog.create_error_log (dml_table_name => ' A ', err_log_table_name => ' A_log ');
Law II:
Create error log tables manually
However, it should be noted that the manually created error log table structure needs to meet Oracle requirements, or it will be an error.
2
Executes the DML statement with the error logging parameter, with the INSERT syntax structure as follows I didn't find either update or delete
Related syntax
insert_into_clause {values_clause [returning_clause] | subquery}
[Error_logging_clause]

3
The Query error log table is the same as the general SELECT statement.
I'll take the insert operation as a test case below.

--Create a test table
CREATE TABLE A As
SELECT rownum id,rownum+1 VALUE
From All_objects
WHERE rownum<3;

--Create a unique index
CREATE UNIQUE INDEX unq_a on a (ID);

--Using 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-Error Records
ora_err_mesg$ VARCHAR2 (2000)--Error message
ora_err_rowid$ ROWID--ROWID of the error record (update or delete only)
ora_err_optyp$ VARCHAR2 (2)--type of operation (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 from the original table.
Please note that the field type of the original table and the corresponding error log table is not exactly matched,

The conversion rules are as follows

:

Number VARCHAR2 (4000)

CHAR/VARCHAR2 (n) VARCHAR2 (4000)

NCHAR/NVARCHAR2 (n) NVARCHAR2 (4000)

Date/timestamp VARCHAR2 (4000)

Raw Raw (2000)

ROWID Urowid

Long/lob

Insert data, rownum<3 Records violate constraints, ROWNUM=3 records are inserted normally

Sql> INSERT into a

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: Here is unlimited, that is, do not limit the number of error records, the default is 0, that is, there is an error record to stop the entire transaction and rollback, we can also artificially set a number,
--assuming 20, that is, when the number of error records inserted exceeds 20, the entire transaction is stopped and rolled back