This discussion is archived
2 Replies Latest reply: Mar 20, 2013 1:37 PM by €$ħ₪ RSS

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

998070 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points