Oracle CHANGE_DUPKEY_ERROR_INDEX Hint Example

In this tutorial, let us examine the hint CHANGE_DUPKEY_ERROR_INDEX introduced in 11g, and see how this can be used in DML operations in Oracle. We will provide different ways to use this hint with suitable examples as we move on. From the Oracle documentation, this hint helps to redirect one of the unique index exceptions that gets thrown during an INSERT or UPDATE operation to a different ORA error code. This helps the application team to capture such errors easily using the error code and provide suitable error messages back to the calling application.With this amount of theory, let us see this hint in action. The examples would help better to understand the functionality.

Basic Example of CHANGE_DUPKEY_ERROR_INDEX in Oracle

Here we create a simple table book, with a primary key on book Id. We also create a unique index on Owner ID at the same time, assuming one person can take only one book at a time. We then insert couple of records into the table to see the hint in action. The rest of the steps are commented in the script for better understanding.

PDB1@ORCL> create table book
  2  (
  3  bookid number primary key,
  4  ownerid number,
  5  booktype varchar2(20)
  6  )
  7  /

Table created.

PDB1@ORCL> create unique index b_owner on book(ownerid)
  2  /

Index created.

PDB1@ORCL> insert into book values (1,1001,'Comic')
  2  /

1 row created.

PDB1@ORCL> insert into book values (2,1002,'Story');

1 row created.

***************************************************

Oracle throws ORA-0001 regardless of which constraint gets violated during the insert operation as shown below. There is a trouble understanding the reason for the insert failure, unless we look in detail into the error message text, parse it and find out the offending column.

******************************************************
PDB1@ORCL> insert into book values (1,1003,'Puzzle')
  2  /
insert into book values (1,1003,'Puzzle')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.SYS_C0013399) violated


PDB1@ORCL> insert into book values (3,1001,'Puzzle')
  2  /
insert into book values (3,1001,'Puzzle')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.B_OWNER) violated

*******************************************************

Enter the new hint, it takes the table name and index name / column name that comprise the unique index, so when that particular unique index is offended due to INSERT, Oracle gives a different error code - ORA 38911. Now, the developer can just look into the error code and understand that the problem is due to Owner ID column and provide suitable message back on the application. You can see that the error codes when this hint is used across the two unique indexes are now different.  

PDB1@ORCL> insert /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK(OWNERID)) */ into BOOK Values (3,1001,'Puzzle'); 
insert /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK(OWNERID)) */ into BOOK Values (3,1001,'Puzzle')
*
ERROR at line 1:
ORA-38911: unique constraint (HR.B_OWNER) violated


PDB1@ORCL> insert /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK(OWNERID)) */ into BOOK Values (1,1004,'Puzzle');
insert /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK(OWNERID)) */ into BOOK Values (1,1004,'Puzzle')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.SYS_C0013399) violated

Error Code when Both the indexes are offended - For the example I tried, Oracle still throws 38911 when both the indexes are violated.

PDB1@ORCL> insert /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK(OWNERID)) */ into BOOK Values (1,1001,'Puzzle');
insert /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK(OWNERID)) */ into BOOK Values (1,1001,'Puzzle')
*
ERROR at line 1:
ORA-38911: unique constraint (HR.B_OWNER) violated
************************************************************************

Specifying Index Name  in  CHANGE_DUPKEY_ERROR_INDEX Hint

In the example above, we specified the column name inside the hint. It is also possible to specify the index name in the hint instead of column name. This is shown below with an example;

PDB1@ORCL> insert /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK,B_OWNER) */ into BOOK Values (3,1001,'Puzzle');

insert /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK,B_OWNER) */ into BOOK Values (3,1001,'Puzzle')
*
ERROR at line 1:
ORA-38911: unique constraint (HR.B_OWNER) violated
 

Using the Hint with Update Statements

As per Oracle documentation this index can also be used with update statements. However, I noted no difference to the error code behaviour when I tried using it in UPDATE SQL. You may wish to review the below and comment in this post, if this scenario is working in your case.

PDB1@ORCL> UPDATE /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK,B_OWNER) */ BOOK SET OWNERID=1002 where OWNERID=1001;
UPDATE /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK,B_OWNER) */ BOOK SET OWNERID=1002 where OWNERID=1001
*
ERROR at line 1:
ORA-00001: unique constraint (HR.B_OWNER) violated


PDB1@ORCL> UPDATE /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK(OWNERID)) */ BOOK SET OWNERID=1002 where BOOKID=1
  2  /
UPDATE /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK(OWNERID)) */ BOOK SET OWNERID=1002 where BOOKID=1
*
ERROR at line 1:
ORA-00001: unique constraint (HR.B_OWNER) violated

Using CHANGE_DUPKEY_ERROR_INDEX in PL SQL Procedure

One of the biggest use of this hint is in a PL/SQL stored procedure / function where you can catch the exact index that is violating the rule by handling it in a different exception block. In such cases, the procedure can take different action based on the exception received and there is no need to parse exception text any more to understand which index is offended. A sample PL / SQL procedure that explains this usage is provided below:

CREATE OR REPLACE PROCEDURE CATCH_INSERT_ERRORS AS
  OWNER_ID_VIOLATED EXCEPTION;
  pragma exception_init(OWNER_ID_VIOLATED, -38911);
BEGIN
  BEGIN
    INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK(OWNERID)) */
    INTO BOOK
    VALUES
      (3, 1001, 'PUZZLE');
  EXCEPTION
    WHEN OWNER_ID_VIOLATED THEN
      DBMS_OUTPUT.PUT_LINE('Exception in Owner ID - Check Input Value');
    WHEN dup_val_on_index then
      DBMS_OUTPUT.PUT_LINE('Exception in Book ID - Check Input Value');
  END;

  BEGIN
    INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK(OWNERID)) */
    INTO BOOK
    VALUES
      (1, 1004, 'PUZZLE');
  EXCEPTION
    WHEN OWNER_ID_VIOLATED THEN
      DBMS_OUTPUT.PUT_LINE('Exception in Owner ID - Check Input Value');
    WHEN dup_val_on_index then
      DBMS_OUTPUT.PUT_LINE('Exception in Book ID - Check Input Value');
  END;

END CATCH_INSERT_ERRORS;

Finally, some of the common errors that you may encounter when using this hint inside your SQL is provided below. A possible resolution to the error code is also provided.

ORA 38913 - Index Specified in the index hint is invalid

You get this error if the index name / column name you specified inside the hint is not a valid one. You need to change your SQL query to use the right column name - an example where such an error can happen is shown below. To solve this, you will have to fix your column / index name.

SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK(OWNERID1)) */ INTO BOOK VALUES  (3, 1001, 'PUZZLE');
 
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK(OWNERID1)) */ INTO BOOK VALUES  (3, 1001, 'PUZZLE')
 
ORA-38913: Index specified in the index hint is invalid

ORA 38912 - An index must be specified in the index hint

You get this error if you have not specified index / column name in the hint and left it as per an example below. To fix this problem, you have to add index / column name to the hint.

SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK) */ INTO BOOK VALUES  (3, 1001, 'PUZZLE');
 
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK) */ INTO BOOK VALUES  (3, 1001, 'PUZZLE')
 
ORA-38912: An index must be specified in the index hint

ORA 38914 - Either mutate the error or ignore row on unique violation

You get this error when you combine this hint with IGNORE_ROW_ON_DUPKEY_INDEX hint in the same SQL statement. You cannot use them together and have to use only one of them. A simulation is shown below:

SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK,B_OWNER) IGNORE_ROW_ON_DUPKEY_INDEX (BOOK,BOOKID) */ INTO BOOK VALUES  (3, 1001, 'PUZZLE');
 
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK,B_OWNER) IGNORE_ROW_ON_DUPKEY_INDEX (BOOK,BOOKID) */ INTO BOOK VALUES  (3, 1001, 'PUZZLE')
 
ORA-38914: Either mutate the error or ignore row on unique violation

ORA 38915 - Multiple indexes in change or ignore duplicate key hint

You can specify only one index inside the hint. when you use the dupkey clause more than once with multiple indexes you will get this error. Fix your insert statement to resolve this error.

SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK,B_OWNER) CHANGE_DUPKEY_ERROR_INDEX (BOOK,BOOKID) */ INTO BOOK VALUES  (3, 1001, 'PUZZLE');
 
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(BOOK,B_OWNER) CHANGE_DUPKEY_ERROR_INDEX (BOOK,BOOKID) */ INTO BOOK VALUES  (3, 1001, 'PUZZLE')
 
ORA-38915: Multiple indexes in change or ignore duplicate key hint