Error message looks like this
Error message => ORA-00001: unique constraint (schema.unique_constraint_name) violated
ORA-00001 occurs when: «a query tries to insert a «duplicate» row in a table». It makes an unique constraint to fail, consequently query fails and row is NOT added to the table.»
Solution:
Find all columns used in unique_constraint, for instance column a, column b, column c, column d collectively creates unique_constraint and then find the record from source data which is duplicate, using following queries:
-- to find <<owner of the table>> and <<name of the table>> for unique_constraint
select *
from DBA_CONSTRAINTS
where CONSTRAINT_NAME = '<unique_constraint_name>';
Then use Justin Cave’s query (pasted below) to find all columns used in unique_constraint:
SELECT column_name, position
FROM all_cons_columns
WHERE constraint_name = <<name of constraint from the error message>>
AND owner = <<owner of the table>>
AND table_name = <<name of the table>>
-- to find duplicates
select column a, column b, column c, column d
from table
group by column a, column b, column c, column d
having count (<any one column used in constraint > ) > 1;
you can either delete that duplicate record from your source data (which was a select query in my particular case, as I experienced it with «Insert into select») or modify to make it unique or change the constraint.
Have you gotten an “ORA-00001 unique constraint violated” error? Learn what has caused it and how to resolve it in this article.
ORA-00001 Cause
If you’ve tried to run an INSERT or UPDATE statement, you might have gotten this error:
ORA-00001 unique constraint violated
This has happened because the INSERT or UPDATE statement has created a duplicate value in a field that has either a PRIMARY KEY constraint or a UNIQUE constraint.
There are a few solutions to the “ORA-00001 unique constraint violated” error:
- Change your SQL so that the unique constraint is not violated.
- Change the constraint to allow for duplicate values
- Drop the constraint from the column.
- Disable the unique constraint.
Solution 1: Modify your SQL
You can modify your SQL to ensure you’re not inserting a duplicate value.
If you’re using ID values for a primary key, it’s a good idea to use a sequence to generate these values. This way they are always unique.
You can use the sequence.nextval command to get the next value of the sequence.
So, instead of a query like this, which may not work if the employee_id value is already used:
INSERT INTO employee (employee_id, first_name, last_name)
VALUES (231, 'John', 'Smith');You can use this:
INSERT INTO employee (employee_id, first_name, last_name)
VALUES (seq_emp_id.nextval, 'John', 'Smith');Assuming the sequence is set up correctly, this should ensure that a unique value is used.
Find the constraint that was violated
The “ORA-00001 unique constraint violated” error usually shows a name of a constraint. This could be a descriptive name (if you’ve named your constraints when you create them) or a random-looking name for a constraint.
You can query the all_indexes view to find the name of the table and other information about the constraint:
SELECT *
FROM all_indexes
WHERE index_name = <constraint_name>;This will give you more information about the specific fields and the table.
Solution 2: Change the constraint to allow for duplicates
If you have a unique constraint or primary key set up on your table, you could change the constraint to allow for duplicate values, to get around the ORA-00001 error.
Let’s say the unique constraint applies to first_name and last_name, which means the combination of those fields must be unique.
If you find that that rule is incorrect, you can change the constraint to say that the combination of first_name, last_name, and date_of_birth must be unique.
To do this, you need to drop and recreate the constraint.
To drop the constraint:
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;Then, recreate the constraint:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (col1, col2....);Now your constraint will reflect your rules.
Solution 3: Remove the unique constraint
The third solution would be to drop the unique constraint altogether.
This should only be done if it is not required.
To do this, run the ALTER TABLE command:
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;The constraint will be removed and you should be able to UPDATE or INSERT the data successfully.
Solution 4: Disable the unique constraint
The final solution could be useful if you’re doing a lot of data manipulation and you need to temporarily disable the constraint, with the aim of enabling it later.
Disabling the constraint will leave it in the data dictionary and on the table, with the same name, it just won’t be checked when data is inserted or updated.
To disable the constraint:
ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;If you need to enable the constraint in the future:
ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;So, that’s how you can resolve the “ORA-00001 unique constraint violated” error.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!
Problem
Error message running a stream exporting data to Oracle:
23000[1][520][ODBC Oracle Wire Protocol Driver][Oracle]ORA-00001: unique constraint (xxx) violated
(where xxx stands for the constraint name)
Cause
You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.
Resolving The Problem
The option(s) to resolve this Oracle error are:
1) Drop the unique constraint.
2) Change the constraint to allow duplicate values.
3) Modify your SQL so that a duplicate value is not created.
If you are not sure which unique constraint was violated, you can run the following SQL:
SELECT DISTINCT table_name
FROM all_indexes
WHERE index_name = ‘xxx’; (where xxx stands for the constraint name from the error message)
Ref below url’s for more info:
https://docs.oracle.com/cd/E11882_01/server.112/e17766/e0.htm
http://www.techonthenet.com/oracle/errors/ora00001.php
So basically this issue happens if you attempt to insert an already existing value into a column defined as requiring unique data. You may have to check with your Oracle DBA and see if that constraint has been set and if so if it can be removed, as mentioned above, so the export can be completed. Either
way the issue is with the Oracle Database you are trying to write to and will have to be taken care of on the Oracle side
Related Information
[{«Product»:{«code»:»SS3RA7″,»label»:»IBM SPSS Modeler»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Modeler Server»,»Platform»:[{«code»:»PF025″,»label»:»Platform Independent»}],»Version»:»Not Applicable»,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]
Learn the cause and how to resolve the ORA-00001 error message in Oracle.
Description
When you encounter an ORA-00001 error, the following error message will appear:
- ORA-00001: unique constraint (constraint_name) violated
Cause
You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
Drop the unique constraint.
Option #2
Change the constraint to allow duplicate values.
Option #3
Modify your SQL so that a duplicate value is not created.
Note
If you are not sure which unique constraint was violated, you can run the following SQL:
SELECT DISTINCT table_name FROM all_indexes WHERE index_name = 'CONSTRAINT_NAME';
In our example (see picture above), our constraint name would be SYS_C002459 and we would execute the following SQL:
SELECT DISTINCT table_name FROM all_indexes WHERE index_name = 'SYS_C002459';
This would return the name of the table whose unique constraint we violated.
Описание некоторых распространенных сообщений об ошибках Oracle
ORA-00001 : unique constraint violated — Вы пытаетесь выполнить вставку или изменение поля, значение которого будет нарушать ограничение уникальности поля.
ORA-01001 : invalid cursor — Вы пытаетесь использовать несуществующий курсор. Вероятная причина – курсор не был открыт, или уже закрыт.
ORA-01012 : not logged on — Вы не авторизованы, но пытаетесь выполнить запрос. Попробуйте залогиниться и повторить запрос.
ORA-01017 : invalid username/password; logon denied — Неверная комбинация логин/пароль.
ORA-01476 : divisor is equal to zero — Вы пытаетесь выполнить деление на ноль.
ORA-01000 : maximum open cursors exceeded — количество открытых курсоров превысило значение параметра OPEN_CURSORS базы данных. Уменьшите количество используемых курсоров в БД в Вашей программе. Если это не помогло, затормозите сервис БД, увеличьте значение параметра OPEN_CURSORS в init.ora и перезапустите сервис БД.
ORA-06511 : PL/SQL: cursor already open — Вы пытаетесь открыть уже открытый курсор
ORA-12545: Connect failed because target host or object does not exist – что-то не то с хостом или ip-адресом, с которым вы пытаетесь соединиться. Проверьте существование хоста командами tnsping в unix и ping в windows.
ORA-12541 : TNS:no listener – Убедитесь, что на сервере запущен прослушивающий процесс (листенер). Если нет, то используйте команду lsnrctl, либо запустите сервис в службах Windows. Если процесс запущен, проверьте правильность настроек файла listener.ora.
ORA-12500 : TNS:listener failed to start a dedicated server process – вероятно, сервис базы данных не запущен. Для Windows проверьте что сервис запущен, обычно сервис имеет имя OracleService, где SID – название базы данных. Для Unix убедитесь, что запущен процесс smon при помощи команды ps: ps -ef | grep ora_smon
ORA-12154 : TNS:could not resolve service name – Вы пытаетесь выполнить подключение к БД, которая неизвестна Вашему клиенту. Проверьте существование и настройки файла tnsnames.ora.
ORA-12514 : TNS:listener does not currently know of service requested in connect descriptor – Во-первых, проверьте что запущен сервис базы данных. Ошибка также может возникать, если сервис БД и листенер еще стартуют, в этом случае подождите минуту-две. Если сервисы запущены, то причина ошибки – сервисы БД и листенер не настроены друг на друга. Проверьте настройки файлов tnsnames.ora и listener.ora.
ORA-12560 : TNS:protocol adapter error – вероятно, не запущен сервис базы данных. Запустите сервис в службах Windows или выполните команду startup в sqlplus.
ORA-20001 : A user specified error message – ошибки с кодами 20000-20999 отданы на откуп разработчикам приложений. Разработчики могут возбуждать такие ошибки в своих приложениях функцией raise_application_error. Обратитесь к разработчикам приложения за более подробной информацией.
Содержание
- Error ‘ORA-00001: UNIQUE CONSTRAINT (AP.AP_INVOICE_DISTRIBUTIONS_U1) VIOLATED’ During Invoice Validation (Doc ID 2443983.1)
- Applies to:
- Symptoms
- Cause
- To view full details, sign in with your My Oracle Support account.
- Don’t have a My Oracle Support account? Click to get started!
- MERGE STATEMENT FAILS WITH ORA-00001 UNIQUE CONSTRAINT VIOLATION (Doc ID 1081283.1)
- Applies to:
- Symptoms
- Cause
- To view full details, sign in with your My Oracle Support account.
- Don’t have a My Oracle Support account? Click to get started!
- ORA-00001: unique constraint violated error
- Answers
- ORA-00001: unique constraint error..
- Answers
- ORA-00001: unique constraint violated
Error ‘ORA-00001: UNIQUE CONSTRAINT (AP.AP_INVOICE_DISTRIBUTIONS_U1) VIOLATED’ During Invoice Validation (Doc ID 2443983.1)
Last updated on JULY 15, 2022
Applies to:
Symptoms
On : 12.1.X/12.2.X version, GST Payables
When User is validating invoice, he is getting following error:-
Unexpected error occurred during Tax Calculation.
Exception: Encountered the error in
JAI_TAX_PROCESSING_PKG.CALCULATE_TAXORA-20001: —1:ORA-00001: unique
constraint
(AP.AP_INVOICE_DISTRIBUTIONS_U1) violated.
Please correct the problem or contact your System Administrator.
In FND Debug log message, following error message has been captured.
JAI.PLSQL.JAI_TAX_DETERMINATION_PKG.jai_insert_ap_inv_dist() ORA-00001:
unique constraint (AP.AP_INVOICE_DISTRIBUTIONS_U1) violated
fnd.plsql.APP_EXCEPTION.RAISE_EXCEPTION.dict_auto_log —1: ORA-00001: unique
constraint (AP.AP_INVOICE_DISTRIBUTIONS_U1) violated
JAI.PLSQL.JAI_TAX_PROCESSING_PKG.CALCULATE_TAX ORA-20001: —1: ORA-00001:
unique constraint (AP.AP_INVOICE_DISTRIBUTIONS_U1) violated
STEPS
————————
The issue can be reproduced at will with the following steps:
1. Create Invoice.
2. Validate the invoice
BUSINESS IMPACT
————————
The issue has the following business impact:
Due to this issue, users cannot make vendor payment on time.
Cause
To view full details, sign in with your My Oracle Support account.
Don’t have a My Oracle Support account? Click to get started!
In this Document
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.
Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | 
| 
| 
| Legal Notices | Terms of Use
Источник
MERGE STATEMENT FAILS WITH ORA-00001 UNIQUE CONSTRAINT VIOLATION (Doc ID 1081283.1)
Last updated on FEBRUARY 02, 2022
Applies to:
Oracle Database — Enterprise Edition — Version 10.2.0.4 to 11.2.0.1.0 [Release 10.2 to 11.2]
Oracle Database — Enterprise Edition — Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.
Symptoms
MERGE STATEMENT AFTER UNCOMMITTED UPDATE FAILS WITH ORA-1
The problem can be reproduced as follows:
1.
Session #1
SQL*Plus: Release 10.2.0.4.0 — Production on Thu Jan 21 16:40:52 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 — Production With
the Partitioning, OLAP, Data Mining and Real Application Testing options
2.
SQL> CREATE TABLE table1
(
ORDER_LINE_ID NUMBER(19) NOT NULL,
click NUMBER NOT NULL,
CONSTRAINT PK3
PRIMARY KEY
( ORDER_LINE_ID)
)
ORGANIZATION INDEX;
3.
SQL> CREATE TABLE TABLE2
(
ORDER_LINE_ID NUMBER,
CLICK NUMBER
);
Table created.
4.
SQL> Insert into TABLE1 (ORDER_LINE_ID, CLICK) Values (10430001, 20);
1 row created.
SQL> Insert into TABLE2 (ORDER_LINE_ID, CLICK) Values (10210001, 10);
1 row created.
SQL> Insert into TABLE2 (ORDER_LINE_ID, CLICK) Values (10210001, 20);
1 row created.
SQL> Insert into TABLE2 (ORDER_LINE_ID, CLICK) Values (10430001, 50);
1 row created.
SQL> commit;
Commit complete.
5.
SQL> UPDATE table1 set click=click+5 WHERE order_line_id=10430001;
1 row updated.
SQL> connect login/password
Connected.
SQL> set autotrace on explain
SQL> MERGE INTO table1 t1
USING(
SELECT
fact.ORDER_LINE_ID,
sum (fact.click
) AS click
FROM table2 fact
GROUP by fact.ORDER_LINE_ID
ORDER BY fact.ORDER_LINE_ID
) t2 ON (
t1.ORDER_LINE_ID = t2.ORDER_LINE_ID
)
WHEN MATCHED THEN
UPDATE SET
t1.click = t2.click+10
WHEN NOT MATCHED THEN
INSERT (
ORDER_LINE_ID,
click
) VALUES (
t2.ORDER_LINE_ID,
t2.click) ;
MERGE INTO table1 t1
(the session is waiting)
7.
Go back to Session #1 and enter commit
SQL> commit;
Commit complete.
Now i see in session 1 the following about the waiting MERGE statement:
*
ERROR at line 1:
ORA-00001: unique constraint (OWNER.PK3) violated
8.
Go to session #2 and re-run the merge sql
SQL> /
2 rows merged.
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
| 0 | MERGE STATEMENT | | 2 | 130 | 4 (25)|
00:00:01
|
| 1 | MERGE | TABLE1 | | | |
|
| 3 | NESTED LOOPS OUTER | | 2 | 170 | 4 (25)|
00:00:01
|
| 4 | VIEW | | 2 | 52 | 4 (25)|
00:00:01
|
| 5 | SORT GROUP BY | | 2 | 52 | 4 (25)|
00:00:01
|
| 6 | TABLE ACCESS FULL| TABLE2 | 2 | 52 | 3 (0)|
00:00:01
|
|* 7 | INDEX UNIQUE SCAN | PK3 | 1 | 59 | 0 (0)|
00:00:01
|
Note
——
— dynamic sampling used for this statement
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
| 0 | MERGE STATEMENT | | 2 | 130 | 4 (25)|
00:00:01
|
| 1 | MERGE | TABLE1 | | | |
|
| 3 | NESTED LOOPS OUTER | | 2 | 170 | 4 (25)|
00:00:01
|
| 4 | VIEW | | 2 | 52 | 4 (25)|
00:00:01
|
| 5 | SORT GROUP BY | | 2 | 52 | 4 (25)|
00:00:01
|
| 6 | TABLE ACCESS FULL| TABLE2 | 2 | 52 | 3 (0)|
00:00:01
|
|* 7 | INDEX UNIQUE SCAN | PK3 | 1 | 59 | 0 (0)|
00:00:01
|
Note
——
dynamic sampling used for this statement
WORKAROUND:
————
COMMIT update before running MERGE command.
Cause
To view full details, sign in with your My Oracle Support account.
Don’t have a My Oracle Support account? Click to get started!
In this Document
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.
Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use
Источник
ORA-00001: unique constraint violated error
Table T has four columns A,B,C,D all are not null. Also, unique constraint is defined on column A & B
but, one of the insert statement got failed with the below error.
Getting DB Error: ORA-00001: unique constraint violated error on inserting the value.
It means already record is existing in the table.
As, thousands of records are getting inserted online in the table at a time, so could not able to trace which record is being inserted.
So, how to find that record in the table which is already existing due to which above unique constraint violation error is raised ?
Answers
Error: ORA 1
Text: unique constraint violated
——————————————————————————-
Cause: An update or insert statement attempted to insert a duplicate key.
Action: Either remove the unique restriction or do not insert the key.
For Trusted Oracle7 configured in DBMS MAC mode, you may see this
message if a duplicate entry exists at a different level.
check in the insert statement which was already in the table.. and also constraint assigned on that column.. if unique constarint then give another value
Thanks
but, I want to find that record in the table and want to delete, so how to find that record ?
Edited by: user640001 on Sep 27, 2010 12:00 AM
can you please post the transaction which you are inserting.
As all transactions are online, so neither can trace the transactions nor have any record details which is creating the problem.
From the db, I came to know that a composite primary key is defined including two columns A,B which is restricting that record.
ALTER TABLE T ADD (
CONSTRAINT T_PK
PRIMARY KEY
(A,B));
Just having the error details and knowing that insert statement is failing due to above constraint
And why do you need to identify the statement which is trying to violate this constraint?
Perhaps starting there will give us a little background information we’re presently missing.
I want to delete that record.
so, I need sql statement to find out that record.
user640001 wrote:
Hi,
I want to delete that record.
so, I need sql statement to find out that record.
The question of «why» still hasn’t really been answered.
If the inserts are coming from an application, and the table has a unique constraint placed on it to protect the data, WHY do you think the application attempting to violate this constraint has «better» data?
If this is indeed the way the application needs to function, then you need to modify the application to account for this.
Instead of the current Which doesn’t attempt to catch the unique constraint error.
Since you do not at present know the data trying to violate the constraint (that’s your entire question) i find it difficult to understand how you could presume to know that it is correct . you know nothing about it so the course you are on seems to be the incorrect one (my opinion anyway).
Источник
ORA-00001: unique constraint error..
Hi There,
We were trying to do an insert when we started having ORA-00001: unique constraint error.. to speed our testing we decided to disable all the constraints on the table; however we still having the same issue.
How can we resolve this please.
Anything else we can do please?
Answers
but isn’t the unique index constraint part of the disabled constraints on the table as shown above?
I’m surprised that after we disabled all constraints on the table, this is still an issue??
We can certainly drop it for the time being.. will keep you posted.
rsar001 wrote:
but isn’t the unique index constraint part of the disabled constraints on the table as shown above?
I’m surprised that after we disabled all constraints on the table, this is still an issue??
We can certainly drop it for the time being.. will keep you posted.
Not if index used by PK was created separately prior to PK:
But by dropping index you are simply delaying the issue. Yes, you will be able to insert, but what’s then? You will not be able to recreate PK — same violation error will be raised.
One should keep an eye out for constraints forced through unique indexes and/or any other objects.
We completely missed out the fact that the constraints here are forced through the unique index created before creating the PK.
Источник
ORA-00001: unique constraint violated
I am getting very strange problem here , i don’t understand where is the issue and what is the solution.
Database version 11.2.0.4.0
I have a table let’s say
tb_legal_arb_execution
Composite primary key (comp_code,sec_requisition_no)
Insert statement for inserting data into the table giving error ORA-00001: unique constraint violated
The get_exe_requisition_no is function which is written inside the package legal_requisition_package.
This legal_requisition_package.get_exe_requisition_no is generating the sec_requisition_no for the table tb_legal_arb_execution
which is SE/0018/C/0715/00002 is correct.
The result of the above query(which is used in the insert statement) is giving the correct result as expected.
| COMP_CODE | REQUISITION_NO | SEC_REQUISITION_NO | PROPOSAL_NO | TRUNC(SYSDATE) | ZONE_CODE | REGION_CODE | BRANCH_CODE | LOCATION_CODE | SENT_ARB_APRV | ARB_APPROVE_ID | ARB_APPROVE_DATE |
| 000002 | LG/0018/C/0714/00004 | SE/0018/C/0715/00002 | PG/0018/C/11/000102 | 22-Jun-2016 | 000002 | 000012 | 000018 | 0018 | Y | USER | 22-Jun-2016 |
If the insert statement written directly like this then record is inserted successfully.
Why the record is not inserting into the table while using the package and function ?
Источник
ORA-00001 unique constraint violated is one of the common messages we often get while loading data.
This ORA-00001 unique constraint violated error occurs when You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.
We can perform the following action items for this ORA-00001
(1) You can look at the error and find the constraint information with the below sql
SELECT column_name, position FROM all_cons_columns WHERE constraint_name = '<name of the constraint in error>' AND owner = '<table owner>' AND table_name = '<table name>'
Now we can check the existing data with the data we are inserting and then take action accordingly. You can change the keys so that they can be inserted
CREATE TABLE EXAMPLE_UNIQ(
EXAM_ID NUMBER NOT NULL ENABLE,
EXAM_NAME VARCHAR2(250) NOT NULL ENABLE
CONSTRAINT UK1_EXAMPLE UNIQUE (EXAM_ID, EXAM_NAME) ENABLE
);
table created.
INSERT INTO EXAMPLE_UNIQ (EXAM_ID, EXAM_NAME) VALUES (1000, 'XYZ');
1 rows inserted.
Commit;
INSERT INTO EXAMPLE_UNIQ (EXAM_ID, EXAM_NAME) VALUES (1000, 'XYZ');
SQL Error: ORA-00001: unique constraint (UK1_EXAMPLE) violated
Solution
INSERT INTO EXAMPLE_UNIQ (EXAM_ID, EXAM_NAME) VALUES (1001, 'XYZ');
1 rows inserted.
(2) We can drop the constraint if duplicates are allowed in the table
Example
alter table <table name> drop constraint <constraint name>;
(3) The 11gr2 hint ignore_row_on_dupkey_index allows the statement to silently ignore ORA-00001 errors.
- The IGNORE_ROW_ON_DUPKEY_INDEX hint are unlike other hints in that they have a semantic effect. The general philosophy explained in “Hints” does not apply for these three hints.
- The IGNORE_ROW_ON_DUPKEY_INDEX hint applies only to single-table INSERT operations. It is not supported for UPDATE, DELETE, MERGE, or multitable insert operations. IGNORE_ROW_ON_DUPKEY_INDEX causes the statement to ignore a unique key violation for a specified set of columns or for a specified index. When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row. If you specify this hint when inserting data with DML error logging enabled, then the unique key violation is not logged and does not cause statement termination.
The semantic effect of this hint results in error messages if specific rules are violated:
- If you specify index, then the index must exist and be unique. Otherwise, the statement causes ORA-38913.
- You must specify exactly one index. If you specify no index, then the statement causes ORA-38912. If you specify more than one index, then the statement causes ORA-38915.
- You can specify either a CHANGE_DUPKEY_ERROR_INDEX or IGNORE_ROW_ON_DUPKEY_INDEX hint in an INSERT statement, but not both. If you specify both, then the statement causes ORA-38915.
- As with all hints, a syntax error in the hint causes it to be silently ignored. The result will be that ORA-00001 will be caused, just as if no hint were used.
insert /*+ ignore_row_on_dupkey_index(unique_table, unique_table_idx) */ into unique_table (select * from non_unique_table);
Related articles
ORA-00911: invalid character
ORA-03113: end-of-file on communication channel
ORA-00257
ORA-29285: file write error
ORA-29913 with external tables
delete query in oracle
https://docs.oracle.com/cd/E11882_01/server.112/e17766/e0.htm