6

I have a lengthy INSERT statement running on Oracle that fails with:

ORA-00001: unique constraint (MYPROJECT.MYTABLE_PK) violated

The constraint is:

CONSTRAINT "MYTABLE_PK" PRIMARY KEY ("ENCOUNTER_NUM", "CONCEPT_CD", "PROVIDER_ID", "START_DATE", "MODIFIER_CD")

The problematic INSERT statement is logged, but not the values causing the constraint violation.

Is there a way to log or otherwise save and view the values that violated the constraint?

This is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

1
  • Could you please add the version of Oracle that you're using to your question? =)
    – Kassandry
    Nov 6, 2015 at 6:34

1 Answer 1

5

You can use DML error logging

begin
  dbms_errlog.create_error_log( dml_table_name => 'MYTABLE' );
end;
/

insert into mytable
  <<your select statement>>
  log errors into err$mytable
  reject limit unlimited;

This will log all the rows that violated a constraint to the err$mytable table.

2
  • 1
    Or you could drop the constraint - insert all the data and then do a SELECT * FROM My_Table WHERE "ENCOUNTER_NUM" = "ENCOUNTER_NUM" AND "CONCEPT_CD" = "CONCEPT_CD".... &c) - that should give you a list of offending records. BTW, why are you using double-quotes - they'll only mess with your SQL (make it case-sensitive) and make it difficult to port your app should you choose another RDBMS. BTW, I prefer @Justin Cave's solution - much more elegant.
    – Vérace
    Nov 6, 2015 at 13:38
  • Many thanks. And thanks also for the tip on double quotes. I'm a neophyte Oracle user cautiously poking around in legacy code to make it a little less fragile so I will change the quotes. Nov 7, 2015 at 1:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.