Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I run the PL/SQL block after the CREATE statement, I receive the duplicate key error I would expect if I did not add the hint. Any ideas to why the hint does not work?

CREATE TABLE Sandbox.Clue
(
    ID number,
    Clue_Name varchar2(8 BYTE),
    Quantity number,
        PRIMARY KEY (ID)
);

DECLARE
    i number := 1;
BEGIN
    for i in 1 .. 10
    loop
        INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(Sandbox.Clue.ID)*/ INTO Sandbox.Clue
        (
            ID, Clue_Name, Quantity
        )
        VALUES
        (
            mod(i, 2), 'Tip', 44
        );
    end loop;
END;
share|improve this question
4  
What does select * from v$version give you? That hint was introduced in 11.2. Before that version it's not available. –  a_horse_with_no_name Aug 1 '12 at 6:51
    
I am, indeed, using version 11.2. –  user10478 Aug 2 '12 at 20:14
add comment

4 Answers

up vote 4 down vote accepted

The hint you specified was almost correct. In the hint you should specify the correct index_name, or the column list. Given the fact that the index_name is system generated, I would specify the column list.

DECLARE
    i number := 1;
BEGIN
    for i in 1 .. 10
    loop
        INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(Clue(ID))*/ INTO Clue
        (
            ID, Clue_Name, Quantity
        )
        VALUES
        (
            mod(i, 3), 'Tip', 44
        );
    end loop;
END;
/

also see Oracle® Database SQL Language Reference

share|improve this answer
    
Interesting, the hint appears to work only when the table name is NOT qualified by the schema name. I assume you noticed the same, as you omitted schema name completely from the code. –  user10478 Aug 2 '12 at 20:13
    
INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(Clue(ID))*/ INTO Sandbox.Clue –  user10478 Sep 2 '12 at 17:49
    
@user10478 The syntax for the table(column) part of the hint looks similar to that used for the index hint, for which the documentation states to specify the table name without schema name, or the alias if one is used. I'm not sure whether aliases are accepted for single table inserts (the docs suggest that they are) but it would be interesting to see whether the hint needs the alias to be used if one is specified. –  David Aldridge Jun 11 '13 at 9:45
add comment

Your syntax will need either:

table_name,index_name or
table_name(column_name)

in the hint, not just the column name as you have in your example.

share|improve this answer
    
@dezso it shows the syntax explicitly. anony_mouse: I've removed the comment about the link as ypercube has edited in the code. –  Jack Douglas Aug 8 '12 at 12:27
add comment

In APEX, where a user can actually log into a particular schema (thus qualifying table names is optional), the follow code works while logged into Sandbox...

DECLARE
    i number := 1;
BEGIN
    for i in 1 .. 10
    loop
        INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(Clue(ID))*/ INTO Sandbox.Clue
        (
            ID, Clue_Name, Quantity
        )
        VALUES
        (
            mod(i, 3), 'Tip', 44
        );
    end loop;
END;

...however, the following code does not work in the same environment...

DECLARE
    i number := 1;
BEGIN
    for i in 1 .. 10
    loop
        INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(Sandbox.Clue(ID))*/ INTO Sandbox.Clue
        (
            ID, Clue_Name, Quantity
        )
        VALUES
        (
            mod(i, 3), 'Tip', 44
        );
    end loop;
END;

...while the single qualification is the only difference. I also attempted Sandbox(Clue(ID)) inside the hint, which was equally unsuccessful.

share|improve this answer
add comment

Please be aware of BUG#11865420 and BUG#17397545 as duplicates still might be inserted when using the IGNORE_ROW_ON_DUPKEY_INDEX-hint.

I would rather NOT use the hint until the issue on BUG#17397545 has been fixed (and confirmed).

share|improve this answer
1  
Could you add some links to help readers with these bug reports? –  dezso Sep 3 '13 at 12:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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