Hello, does anyone know if CHANGE_DUPKEY_ERROR_INDEX hint works with update statements.
According to oracle documentation:
The CHANGE_DUPKEY_ERROR_INDEX hint provides a mechanism to unambiguously identify a unique key violation for a specified set of columns or for a specified index. When a unique key violation occurs for the specified index, an ORA-38911 error is reported instead of an ORA-001.
This hint applies to **INSERT**, **UPDATE** operations.
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comments.html#GUID-BE83A338-FE21-444F-8CD9-455FC79C0057
With insert the hint works.
insert /*+ CHANGE_DUPKEY_ERROR_INDEX(TABLE_NAME, INDEX_NAME)*/ into TABLE_NAME…..
Error report -
ORA-38911: unique constraint (INDEX_NAME) violated
With update it doesn't.
update /*+ CHANGE_DUPKEY_ERROR_INDEX(TABLE_NAME, INDEX_NAME)*/ TABLE_NAME…..
Error report -
ORA-00001: unique constraint (INDEX_NAME) violated