2 Replies Latest reply: Mar 20, 2013 3:37 PM by €$ħ₪ RSS

    MERGE statement - Source with duplicate values not raising error ora-30926

    998070
      Hi ,

      I'm having a problem with merge that has already been reported in another thread ( Re: MERGE strange behavior ). I created a procedure with a query and have tested inserting two equal values on the Key column to raise the error. But on the first time there i run the Procedure it runs without error, apparently updating with the first row found. On the second time there i run the procedure then Oracle raises the error.

      Above you can see the code to simulate the issue:

      -- creating the table
      create TABLE teste
      ( ID NUMBER NOT NULL , DESCR VARCHAR2 (20 CHAR) , GRUPO VARCHAR2 (30 CHAR) );

      -- inserting the pk in table
      ALTER TABLE Teste
      ADD CONSTRAINT DimTestec_PK PRIMARY KEY ( ID ) ;

      -- inserting records in the table
      insert into teste
      select -2 AS id,'t-2' AS descr,'t-2g' AS grupo from dual
      union all
      select -1 AS id,'t-1' AS descr,'t-1g' AS grupo from dual
      union all
      select 1 AS id,'t1' AS descr,'t1g' AS grupo from Dual
      union all
      select 2 AS id,'t2' AS descr,'t2g' AS grupo from Dual
      union all
      select 3 AS id,'t3' AS descr,'t3g' AS grupo from Dual
      union all
      select 4 AS id,'t4' AS descr,'t4g' AS grupo from Dual
      union all
      select 5 AS id,'t5' AS descr,'t4g' AS grupo from Dual

      --create the procedure to teste the merge

      create or replace procedure mergeteste is

      Begin
      MERGE INTO teste testemerge
      USING ( select -2 AS id,'t-2' AS descr,'t-2g' AS grupo from dual
      union all
      select -1 AS id,'t-1' AS descr,'t-1g' AS grupo from dual
      union all
      select 1 AS id,'t1' AS descr,'t1g' AS grupo from Dual
      union all
      select 2 AS id,'t2' AS descr,'t2g' AS grupo from Dual
      union all
      select 3 AS id,'t3' AS descr,'t3g' AS grupo from Dual
      union all
      select 4 AS id,'t4' AS descr,'t4g' AS grupo from Dual
      union all
      select 5 AS id,'t5' AS descr,'t4g' AS grupo from Dual
      union all
      select 5 AS id,'t6' AS descr,'t6' AS grupo from Dual ) testesource

      ON (testemerge.id = testesource.id)

      WHEN MATCHED THEN

      UPDATE SET testemerge.descr = testesource.descr,
      testemerge.grupo = testesource.grupo

      WHEN NOT MATCHED THEN

      INSERT ( testemerge.id, testemerge.descr, testemerge.grupo )
      VALUES ( testesource.id, testesource.descr, testesource.grupo ) ;

      commit work;

      EXCEPTION
      WHEN OTHERS THEN
      begin
      ROLLBACK;
      dbms_output.put_line(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace);
      end;
      end mergeteste;

      -- the first time you execute the procedure, it runs without error - when it shouldn't run cause there's two id with value 5 on the testesource query.
      -- the second time it raises the expected error.

      Here's the oracle version:

      1 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      2 PL/SQL Release 11.2.0.3.0 - Production
      3 CORE 11.2.0.3.0 Production
      4 TNS for Linux: Version 11.2.0.3.0 - Production
      5 NLSRTL Version 11.2.0.3.0 - Production

      Thanks in advance.

      Best Regards.
      Renan Ribeiro