oracle 如何跳过唯一约束错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33947091/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to skip unique constraint error
提问by ETS
I am trying to insert values into sql table, but I am getting this error in sql query
我正在尝试将值插入到 sql 表中，但在 sql 查询中出现此错误
SQL Error: ORA-00001: unique constraint (uniqueKey) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Is there a way to skip this error and continue insert. Something like this
有没有办法跳过这个错误并继续插入。像这样的东西
try
insert query
catch (unique constraint error)
continue inserting other values
回答by Husqvik
There exists hint ignore_row_on_dupkey_index(<table name>, <unique index name>).
存在提示ignore_row_on_dupkey_index(<table name>, <unique index name>)。
HUSQVIK@hq_pdb_tcp> CREATE TABLE tmp (val NUMBER CONSTRAINT pk_tmp PRIMARY KEY);
Table created.
HUSQVIK@hq_pdb_tcp> INSERT /*+ ignore_row_on_dupkey_index(tmp, pk_tmp) */ INTO tmp (val) SELECT 1 FROM DUAL CONNECT BY LEVEL <= 3;
1 row created.
See that I insert three values of 1 and only one row was created.
看到我插入了三个 1 值，并且只创建了一行。
回答by are
simple sample is insert in for loopand ignore exceptions:
简单示例插入for loop并忽略异常：
begin
for rc in (select * from <your query> loop
begin
insert into t1(...) values (...);
exceptions when others then
null;--ignore any exceptions do nothing
end;
end loop;
end
other sample - same idea but use FORALLbulk operation and SAVE EXCEPTIONS
其他示例 - 相同的想法，但使用FORALL批量操作和SAVE EXCEPTIONS
declare
cursor C is
select ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
decode( mod(rownum,100000), 1, rpad('*',20,'*'), OBJECT_TYPE ) object_type,
CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY
from big_table;
type array is table of c%rowtype;
l_data array;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
l_errors number;
l_errno number;
l_msg varchar2(4000);
l_idx number;
begin
open c;
loop
fetch c bulk collect into l_data limit 100;
begin
forall i in 1 .. l_data.count SAVE EXCEPTIONS
insert into t2 values l_data(i);
exception
when DML_ERRORS then
l_errors := sql%bulk_exceptions.count;
for i in 1 .. l_errors
loop
l_errno := sql%bulk_exceptions(i).error_code;
--do smth with the exceptions
end loop;
end;
exit when c%notfound;
end loop;
close c;
end;
more information see on AskTom and OraMagazine
更多信息请参见 AskTom 和 OraMagazine
https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:1422998100346727312
https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:1422998100346727312
http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html
http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html
回答by Andrew
There is a LOG ERRORSclause which allows you to log the rows that cause errors in to an error table - this error table is created using a DBMS package:
有一个LOG ERRORS子句允许您将导致错误的行记录到错误表中 - 该错误表是使用 DBMS 包创建的：
DBMS_ERRLOG.CREATE_ERROR_LOG(table_being_inserted_into ,name_of_table_for_errors ,NULL,NULL,TRUE);
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_errlog.htm
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_errlog.htm
Function Signature:
函数签名：
DBMS_ERRLOG.CREATE_ERROR_LOG (
dml_table_name IN VARCHAR2,
err_log_table_name IN VARCHAR2 := NULL,
err_log_table_owner IN VARCHAR2 := NULL,
err_log_table_space IN VARCHAR2 := NULL,
skip_unsupported IN BOOLEAN := FALSE);
Then in your insert statement you end it with a log errors clause:
然后在您的插入语句中，您以日志错误子句结束它：
LOG ERRORS INTO your_error_table_name ( 'description of your choosing' ) REJECT LIMIT UNLIMITED;
You can choose to accept a reject limit of a fixed number, allowing you to specify in effect a tolerance to errors before it throws a real error instead of just allowing the row to be place in an error table.
您可以选择接受固定数量的拒绝限制，从而允许您在引发实际错误之前有效地指定对错误的容忍度，而不是仅允许将行放入错误表中。
