Home » SQL & PL/SQL » SQL & PL/SQL » unique constraint violated
unique constraint violated [message #218726] Fri, 09 February 2007 10:08 Go to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Is there a way to deterime which value violated the constraint?

When doing a "insert into table (select * from table)" is there a way to determine what value it failed on? A way to print it out in the "exception when others" clause?

Something like:
alter table no_duplicate_names add (
  constraint pk_no_duplicate_names
 primary key
 (name)
    using index

BEGIN
  insert into no_duplicate_names
    (name)

    (select name
       from duplicate_names)
EXCEPTION
  WHEN others
    THEN
      htp.print('The constraint failed on "insert.value"');
END;
Re: unique constraint violated [message #218731 is a reply to message #218726] Fri, 09 February 2007 11:04 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
Take a look at BULK INSERTS in the PL/SQL manual. It'll let you access two cursor parameters:

%BULK_EXCEPTIONS(i).ERROR_INDEX holds the iteration of the FORALL statement during which the exception was raised.

%BULK_EXCEPTIONS(i).ERROR_CODE holds the corresponding Oracle error code.

You can get the index of the bad record from the first one. Then you can use it to display the bad record in your exception handler.


Here's the example from the manual. You could index back into your collection to show the bad record.


-- create a temporary table for this example
CREATE TABLE emp_temp AS SELECT * FROM employees;

DECLARE
   TYPE empid_tab IS TABLE OF employees.employee_id%TYPE;
   emp_sr empid_tab;
-- create an exception handler for ORA-24381
   errors NUMBER;
   dml_errors EXCEPTION;
   PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
   SELECT employee_id BULK COLLECT INTO emp_sr FROM emp_temp 
         WHERE hire_date < '30-DEC-94';
-- add '_SR' to the job_id of the most senior employees
     FORALL i IN emp_sr.FIRST..emp_sr.LAST SAVE EXCEPTIONS
       UPDATE emp_temp SET job_id = job_id || '_SR' 
          WHERE emp_sr(i) = emp_temp.employee_id;
-- If any errors occurred during the FORALL SAVE EXCEPTIONS,
-- a single exception is raised when the statement completes.

EXCEPTION
  WHEN dml_errors THEN -- Now we figure out what failed and why.
   errors := SQL%BULK_EXCEPTIONS.COUNT;
   DBMS_OUTPUT.PUT_LINE('Number of statements that failed: ' || errors);
   FOR i IN 1..errors LOOP
      DBMS_OUTPUT.PUT_LINE('Error #' || i || ' occurred during '|| 
         'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX); 
          DBMS_OUTPUT.PUT_LINE('Error message is ' ||
          SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
   END LOOP;
END;
/
DROP TABLE emp_temp;

The output from the example is similar to:


Number of statements that failed: 2
Error #1 occurred during iteration #7
Error message is ORA-12899: value too large for column
Error #2 occurred during iteration #13
Error message is ORA-12899: value too large for column

Re: unique constraint violated [message #218733 is a reply to message #218726] Fri, 09 February 2007 11:31 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Likewise, version 10gR2 has a new bulk errors feature that can be used when dealing with errors in sql statements themselves (when your code doesn't use forall or other plsql loop constructs).

But either way (like what was demonstrated above), you can acquire and then process the records that violated the constraint.
Re: unique constraint violated [message #218737 is a reply to message #218726] Fri, 09 February 2007 13:25 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
Didn't now about that! Thanks. That's pretty handy. Here's a link for more info on it.

http://orafaq.com/node/76
Re: unique constraint violated [message #218738 is a reply to message #218737] Fri, 09 February 2007 13:38 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
I like this method much better but it still doesn't show you the actual value that it failed on.

I haven't looked at the docs yet but it looked like the other methods only work with the BULK COLLECT statement. I'm doing a straight "insert into table (select * from table)" and not doing a BULK COLLECT statement.

Maybe I'm missing something about doing an INSERT? I don't see where I need to do a BULK COLLECT if it's just a straight insert into from select statement. Is there more to it than that?
Re: unique constraint violated [message #218741 is a reply to message #218726] Fri, 09 February 2007 13:56 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
You don't need the bulk collect. It was just one way to get the info about what record violated your constraint. If you have 10gR2, then you can use the method smartin proposed. It gives you the error message and the values for the fields. I think you'll have to look at each error to determine what is bad for each case. If you violate a unique constraint defined on more than one field, how would Oracle know which value is incorrect?
Re: unique constraint violated [message #218742 is a reply to message #218741] Fri, 09 February 2007 14:00 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Oh, ok, gotcha.

Good question but I believe the data I'm working with will still allow me to narrow it down some. It's better than nothing.

It sounds like just what I need to complete what I'm doing.
Re: unique constraint violated [message #218745 is a reply to message #218726] Fri, 09 February 2007 15:04 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
handle the dups in the select.

insert into no_duplicate_names (name)
select name from duplicate_names a
where not exists
(select null
from no_duplicate_names b
where a.name = b.name);

No duplicate rows will be inserted.
Previous Topic: Need help for Query
Next Topic: Relating to Aggregate function
Goto Forum:
  


Current Time: Fri Dec 09 04:15:03 CST 2016

Total time taken to generate the page: 0.13264 seconds