MERGE problem
I am experiencing a strange problem with the MERGE command with Oracle version 11.1.0.6.0. I have never used MERGE before and so cannot explain the strange behavior I am experiencing.
What happens is: when the database table is empty the insert portion of the command works perfectly. When I run the same command again, I get a primary key violation ( ORA-00001: Unique Constraint (DBTESTMT.INSTR_DATA_PK) verletzt).
Although the data elements are in the table, the update section of the command seems to fail for the first item and tries to insert it again, causing the pk violation.
I am baffled... Can anyone give me a hint what is happening here?
The offending command is shown below.
--------------------------------------------------------------------------------------------------------
MERGE INTO instr_data d
USING
(
select 'ANN4327C1220' isin, 'GD220B' field, '7' data, ' ' comment_ FROM DUAL UNION -- if this line is commented out, the command works,. If not, then ORA-00001
select 'ANN4327C1220' isin, 'GD267' field, 'A' data, ' ' comment_ FROM DUAL
) s
ON (d.isin = s.isin and d.field = s.field)
WHEN MATCHED THEN
UPDATE set d.data = s.data, d.comment_ = s.comment_
WHEN NOT MATCHED THEN
INSERT(d.isin, d.field, d.data, d.comment_)
VALUES(s.isin, s.field, s.data, s.comment_)
--------------------------------------------------------------------------------------------------------
I created my database table using the following statements...
--------------------------------------------------------------------------------------------------------
CREATE TABLE instr_data (
isin CHAR(12 CHAR) NOT NULL,
field CHAR(10 CHAR) NOT NULL,
data CLOB,
comment_ CLOB
);
ALTER TABLE instr_data
ADD CONSTRAINT instr_data_PK PRIMARY KEY
(
isin,
field
)
ENABLE
;
--------------------------------------------------------------------------------------------------------