1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

dup_val_on_index. ORACLE 11G

Discussion in 'SQL PL/SQL' started by ash, Nov 3, 2014.

  1. ash

    ash Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I have this Insert statement:

    Insert into mytable (x,y,z)Select x,y,z from another table....

    This throws a pk constraint violated error.

    How can i find the row that is causing the problem?
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Ash,

    1) Check the definition of the PK on target table ("mytable") using any tool (SQL, SQL Developer/Toad, ...)
    2) Look for the "duplicate" records in source table - there are several ways to do it (if you google around you'll find sample scripts).

    Let's say, for example, the Primary Key on table "mytable" is on (x,y), then run check for duplicates using something like:

    select * from source_table s1
    where exists (select 1 from source_table s2
    where s2.x = s1.x
    and s2.y = s1.y
    and s2.rowid > s1.rowid)
     
  3. yowancristo

    yowancristo Forum Advisor

    Messages:
    103
    Likes Received:
    10
    Trophy Points:
    305
    Location:
    Bangalore
    Hi Ash,

    You can use Rajen's comments. Below query can be used to get the PK constraint details.

    Code (SQL):

                 
        SELECT  ACC.*,AC.*
        FROM    ALL_CONSTRAINTS AC,
                ALL_CONS_COLUMNS    ACC
        WHERE   AC.TABLE_NAME = 'Target table name'
        AND     AC.constraint_type= 'P'  -- for Primary key constraint.
        AND     AC.constraint_name = ACC.constraint_name;

     
    Regards,
    Yowan Cristo
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    If you want to find the specific row(s) causing the error, you can use a PL/SQL procedure similar to the following (untested) block:

    Code (Text):
    DECLARE
      CURSOR c_test IS
        SELECT x, y, z
        FROM   another_table;
    BEGIN
      FOR v_Lp IN c_test LOOP

        BEGIN
          INSERT INTO mytable VALUES (v_Lp.x, v_Lp.y, v_Lp.z);

        EXCEPTION
          WHEN DUP_VAL_ON_INDEX
            DBMS_OUTPUT.PUT_LINE('x = ' || v_Lp.x);
            DBMS_OUTPUT.PUT_LINE('y = ' || v_Lp.y);
            DBMS_OUTPUT.PUT_LINE('z = ' || v_Lp.z);
        END;
      END LOOP;
      ROLLBACK;
    END;
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yet this will also work:


    Code (SQL):

    SQL> SELECT MAX(rowid)
      2  FROM emp
      3  GROUP BY empno
      4  HAVING COUNT(*) > 1;


    MAX(ROWID)
    ------------------
    AAAEGdAAEAAAACDAAY
    AAAEGdAAEAAAACDAAa
    AAAEGdAAEAAAACDAAb
    AAAEGdAAEAAAACDAAi
    AAAEGdAAEAAAACDAAX
    AAAEGdAAEAAAACDAAh
    AAAEGdAAEAAAACDAAU
    AAAEGdAAEAAAACDAAe
    AAAEGdAAEAAAACDAAj
    AAAEGdAAEAAAACDAAV
    AAAEGdAAEAAAACDAAW
    AAAEGdAAEAAAACDAAZ
    AAAEGdAAEAAAACDAAf
    AAAEGdAAEAAAACDAAg
    AAAEGdAAEAAAACDAAT
    AAAEGdAAEAAAACDAAc
    AAAEGdAAEAAAACDAAS
    AAAEGdAAEAAAACDAAd


    18 ROWS selected.


    Elapsed: 00:00:00.00

    SQL>
     

    If you know that the entire row is a duplicate (not a duplicate key but actually duplicate data) you can delete the 'offending' rows with that same query:


    Code (SQL):

    SQL> SELECT COUNT(*) FROM emp;
      COUNT(*)
    ----------
            36


    1 ROW selected.


    Elapsed: 00:00:00.00
    SQL>
    SQL> DELETE FROM emp
      2  WHERE rowid IN
      3  (SELECT MAX(rowid)
      4  FROM emp
      5  GROUP BY empno
      6  HAVING COUNT(*) > 1);


    18 ROWS deleted.


    Elapsed: 00:00:00.00
    SQL>
    SQL> commit;


    Commit complete.


    Elapsed: 00:00:00.00
    SQL>
    SQL> SELECT COUNT(*) FROM emp;


      COUNT(*)
    ----------
            18


    1 ROW selected.


    Elapsed: 00:00:00.00
    SQL>
     

    The example script I took this from ensured the rows in EMP were duplicates; you should check the data for the duplicate keys and preserve the latest data for that key, and that may not necessarily have the highest ROWID for each grouping.