2

I have a procedure which does a lot of inserts. I am getting a constraint violation, but it is not telling me which insert is causing it. I tried the exception catch below but it is not giving me enough detail.

EXCEPTION WHEN OTHERS THEN
  ROLLBACK;
  DBMS_OUTPUT.PUT_LINE('Procedure failed with:  ' || SQLCODE || ' ' || SQLERRM);
  DBMS_OUTPUT.put_line('Error in '|| $$plsql_unit || ' at ' || $$plsql_line);
Whitey Winn
  • 306
  • 1
  • 6
  • 19
  • what kind of inserts? What about `FOR ALL ... SAVE EXCEPTIONS`? – Sebas Jan 27 '15 at 05:21
  • Check [`dbms_utility`](http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_util.htm#ARPLS73195). See e.g. http://stackoverflow.com/q/16183290/. The question is not exact duplicate but the answer is. – user272735 Jan 27 '15 at 06:08

1 Answers1

1

The way that I have always done this is to wrap each insert in a begin exception block.

so you would end up with 

Begin
  insert statement here
exception when others then
  dbms_output statements
end;

Begin
  insert statement
exception when others
  dbms_output statements
end.

ect

This allows you to add customized output to each insert so you are 100% sure which insert is causing the issue. It takes a bit of work to add but is worth it in the end as it saves you a lot of time in debugging.

Hope this is helpful.

Shaun Peterson
  • 1,735
  • 1
  • 14
  • 19
  • You're potentially doing it suboptimal way (YMMV thought). See e.g. http://stackoverflow.com/a/16187783/272735. In general separate exception blocks are not needed for reporting but only when different actions are required to resolve the exceptions. – user272735 Jan 27 '15 at 06:13