Oracle hint failure

Question

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;

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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).