error_message_details is another new parameter introduced in Oracle version 23ai, which allows displaying detailed information about data value-related errors. By setting this parameter to ON, we can see the exact value that caused errors such as ORA-00001: unique constraint violated within the error message itself.
For example, in the following scenario, enabling the error_message_details parameter helps identify the value that triggered the error:
SQL> create table tbl1(id number primary key);
Table created.
SQL> variable B number;
SQL> exec :B:=2547;
PL/SQL procedure successfully completed.
SQL> insert into tbl1 values(:B);
1 row created.
SQL> insert into tbl1 values(:B);
ERROR at line 1:
'ORA-00001: unique constraint (USEF.SYS_C008328) violated on table USEF.TBL1 columns (ID)'
'ORA-03301: (ORA-00001 details) row with column values (ID:2547) already exists'
Help: https://docs.oracle.com/error-help/db/ora-00001/
If the error_message_details parameter is disabled, the above error will be displayed as follows:
SQL> alter system set error_message_details=DISALLOWED ;
System altered.
SQL> insert into tbl1 values(:B);
'ORA-00001: unique constraint (USEF.SYS_C008329) violated on table USEF.TBL1 columns (ID)'
Help: https://docs.oracle.com/error-help/db/ora-00001/
Top comments (0)