Link to home
Start Free TrialLog in
Avatar of futureDBA
futureDBA

asked on

How can i tell which record is violating a Unique Index on an update?

i have a PL/SQL block that's inserting about 50 records based on a query.

I am getting

SQL Error: ORA-00001: unique constraint (GEN_UQ) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

Open in new window




how can i tell which of the records being inserted is violating the Unique index?
Avatar of Sean Stuber
Sean Stuber

different techniques based on exactly what you're doing.


if you're iterating through a cursor, wrap each insert in a begin/exception/end block to capture the one that is failing.

if you're using a collection to do a bulk insert with forall, then use SAVE EXCEPTIONS and check the
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX, where i is 1..n for the number of exceptions that occured

if you're using insert/select  then use automatic error logging table
or use a before row insert trigger that logs the values
Here is an example of using an error logging table, so your inserts don't fail and you can later review what went wrong:


drop table test_tab purge;
drop table err$_test_tab purge;

create table test_tab(col1 number, col2 number, unique(col1));

--table TEST_TAB created.

insert into test_tab values(1,2);

--1 rows inserted.

insert into test_tab values(1,3);

--SQL Error: ORA-00001: unique constraint (SYS_C0074146) violated.

EXEC dbms_errlog.create_error_log('TEST_TAB');


SELECT * FROM err$_test_tab;

insert into test_tab values(1,3) log errors into err$_test_tab reject limit unlimited;

--0 rows inserted.

select * from err$_test_tab;
if you're doing an insert/select, you can also check before actually doing the insert:
select 
from source_table
where exists( select 1 from destination_table  
        where ... join on index keys ... )

Open in new window


assuming this is a practical approach in your case
Avatar of futureDBA

ASKER

gatorvip.

how would apply that method to this query

INSERT INTO FINAL (ORDER_DATE, CUST_NUM, PROD_NUM, ROUTE_NUM, DESCRIPTION, BETWEEN_NEXT_AND_PREV, BETWEEN_NEXT_TWO, MONTHLYAVG,LAST_DELIVERY,LAST_FRIDGE,GENERATED_QTY,NEXT_DELIVERY_DATE)
WITH M AS (
SELECT 
       CM_CUSTNO,
       CM_STORNO,
       CM_PRIMRY_ROUTNO,
       CM_ZONE,
       CM_CHNID,
next_date,
       prev_date,
       next_date - prev_date between_next_and_prev,
       next_date_after - next_date between_next_two
  FROM (SELECT x.*,
               LEAST(NVL2(m, NEXT_DAY(next_date, 'Mon'), future),
                     NVL2(t, NEXT_DAY(next_date, 'Tue'), future),
                     NVL2(w, NEXT_DAY(next_date, 'Wed'), future),
                     NVL2(r, NEXT_DAY(next_date, 'Thu'), future),
                     NVL2(f, NEXT_DAY(next_date, 'Fri'), future), 
                     NVL2(s, NEXT_DAY(next_date, 'Sat'), future)
               ) next_date_after
          FROM (SELECT c.*,
                       GREATEST(NVL2(m, NEXT_DAY(today - 7, 'Mon'), past),
                                NVL2(t, NEXT_DAY(today - 7, 'Tue'), past),
                                NVL2(w, NEXT_DAY(today - 7, 'Wed'), past),
                                NVL2(r, NEXT_DAY(today - 7, 'Thu'), past),
                                NVL2(f, NEXT_DAY(today - 7, 'Fri'), past),
                                NVL2(s, NEXT_DAY(today - 7, 'Sat'), past)
                       ) prev_date,
                       LEAST(NVL2(m, NEXT_DAY(today, 'Mon'), future),
                             NVL2(t, NEXT_DAY(today, 'Tue'), future),
                             NVL2(w, NEXT_DAY(today, 'Wed'), future),
                             NVL2(r, NEXT_DAY(today, 'Thu'), future),
                             NVL2(f, NEXT_DAY(today, 'Fri'), future),
                             NVL2(s, NEXT_DAY(today, 'Sat'), future)
                        ) next_date
                  FROM (SELECT 
                               CM_CUSTNO,
                               CM_STORNO,
                               CM_CUSTMR_NAME, 
                               CM_PRIMRY_ROUTNO,
                               CM_ZONE,
                               CM_CHNID,
                               NULLIF(cm_primry_dlvseq_1, 0) m,
                               NULLIF(cm_primry_dlvseq_2, 0) t,
                               NULLIF(cm_primry_dlvseq_3, 0) w,
                               NULLIF(cm_primry_dlvseq_4, 0) r,
                               NULLIF(cm_primry_dlvseq_5, 0) f,
                               NULLIF(cm_primry_dlvseq_6, 0) s,
                               SYSDATE - 100 past,
                               TRUNC(SYSDATE) today,
                               SYSDATE + 100 future
                          FROM cusmas2  WHERE CM_CHNID = 'CV' AND  CM_ZONE = '3') c) x)),
                          P AS
                              (SELECT
                                thd_custno,
                                th_prodno,
                                round((sum(th_units)/28),2) LAST30
                                  from
                                          (select 
                                              thd_Custno,
                                              th_prodno,
                                              th_units,
                                              TO_DATE((thd_tranda) || '-' || thd_tranmo || '-' || thd_trancn || thd_tranyr, 'DD-MM-YY') AS sdate
                                          from thsdtl@"DB2")
                                          WHERE thd_custno = '6404' AND SDATE BETWEEN (select trunc(trunc(sysdate,'MM')-1,'MM') from dual) and (select trunc(sysdate,'MM')-1 from dual)
                                          group by 
                                          thd_custno, th_prodno),
                            Q AS(SELECT
                                thd_custno,
                                th_prodno,
                                NVL(th_units,0) as LAST_DEL,
                                TDATE FROM
                                (select outl
                                thd_custno,
                                th_prodno,
                                th_units,
                                TO_DATE((thd_tranda) || '-' || thd_tranmo || '-' || thd_trancn || thd_tranyr, 'DD-MM-YY') as tdate
                                from thsdtl@"DB2")
                                where TH_UNITS >= 0 AND thd_custno = '6404' AND TDATE = (select distinct prevd from dateval where to_date(sysd) = '08-NOV-12' and custno = '6404')) 
Select
    ODATE,
    CUSTNUM,
    PROD_NUM,
    CM_PRIMRY_ROUTNO,
    PROD_DESC,
    BETWEEN_NEXT_AND_PREV,
    BETWEEN_NEXT_TWO,
    NVL(LAST30,0) LAST30,
    NVL(LAST_DEL,0) LAST_DEL,
    oQTY,
    CASE 
      WHEN 0 > (ROUND(((BETWEEN_NEXT_TWO * 1.5) * NVL(LAST30,0)) - ((NVL(LAST_DEL,0) + oQTY) - (NVL(LAST30,0) * BETWEEN_NEXT_AND_PREV))))
                THEN 0
      WHEN ROUND(((BETWEEN_NEXT_TWO * 1.5) * NVL(LAST30,0)) - ((NVL(LAST_DEL,0) + oQTY) - (NVL(LAST30,0) * BETWEEN_NEXT_AND_PREV))) 
             > (SELECT PROD_ALLOWED.MAX FROM PROD_ALLOWED WHERE PROD_ALLOWED.FRIDGE = (select refrigeration FROM fridge where cust_num = '6404' AND PROD_NUM = P.TH_PRODNO))
                THEN (SELECT PROD_ALLOWED.MAX FROM PROD_ALLOWED WHERE PROD_ALLOWED.FRIDGE = (select refrigeration FROM fridge where cust_num = '2028' AND PROD_NUM = P.TH_PRODNO))
      WHEN ROUND(((BETWEEN_NEXT_TWO * 1.5) * NVL(LAST30,0)) - ((NVL(LAST_DEL,0) + oQTY) - (NVL(LAST30,0) * BETWEEN_NEXT_AND_PREV))) 
             <  (SELECT PROD_ALLOWED.MAX FROM PROD_ALLOWED WHERE PROD_ALLOWED.FRIDGE = (select refrigeration FROM fridge where cust_num = '6404' AND PROD_NUM = P.TH_PRODNO))
                THEN ROUND(((BETWEEN_NEXT_TWO * 1.5) * NVL(LAST30,0)) - ((NVL(LAST_DEL,0) + oQTY) - (NVL(LAST30,0) * BETWEEN_NEXT_AND_PREV)))
      ELSE ROUND(((BETWEEN_NEXT_TWO * 1.5) * NVL(LAST30,0)) - ((NVL(LAST_DEL,0) + oQTY) - (NVL(LAST30,0) * BETWEEN_NEXT_AND_PREV)))
      END AS "A - (B - C)",
      next_date
FROM 
  ((ORDERS LEFT JOIN P on P.TH_PRODNO = ORDERS.PROD_NUM) JOIN M ON M.CM_CUSTNO = ORDERS.CUSTNUM) LEFT OUTER JOIN Q ON Q.TH_PRODNO = ORDERS.PROD_NUM
    WHERE CUSTNUM = '6404'
    and to_date(odate) = '08-NOV-12';

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
the simple way would be to create a temp table (without any constraints) on the same lines of orginal table where records are being inserted.

Once all the records are inserted, you can verify which records are inserting duplicate values.

HTH
now your query is like this:
insert into final .... select ...

just do this:
create table final_findproblem as select ...

then check what columns are in unique constraint gen_uq
(probably
       CM_CUSTNO,
       CM_STORNO,
       CM_PRIMRY_ROUTNO,
       CM_ZONE,
       CM_CHNID)

then look for the doubles:

select CM_CUSTNO,
       CM_STORNO,
       CM_PRIMRY_ROUTNO,
       CM_ZONE,
       CM_CHNID, count(*) from final_findproblem
group by CM_CUSTNO,
       CM_STORNO,
       CM_PRIMRY_ROUTNO,
       CM_ZONE,
       CM_CHNID
having count(*) > 1
order by CM_CUSTNO,
       CM_STORNO,
       CM_PRIMRY_ROUTNO,
       CM_ZONE,
       CM_CHNID;

that should give you the doubles
how would apply that method to this query

The caveat in my post was that the approach works if practical - in your case, it may not be, since it's a fairly complex query. You could still make it work with a bit of rearranging the code, but it's probably not worth it. I think the approach suggested by sdstuber and paquicuba would be better in your situation.
why the B?  Since there was no request for followup or corrections a penalty grade is inappropriate