Oracle MERGE statement - Consistent READ

MERGE Statement & constraint violations
Oracle database ALWAYS provides Consistent-Read. This is a very powerful feature in Oracle and there are hundereds of articles on-line about this.
Simple description of Consistent Read is
When a (reading) query starts, it is important that the values of the rows selected are the same as when the query started, even if another session has changed those rows. This is refered to as read consistency. Read consistency is achieved through the SCN (system change number). (REF: http://www.adp-gmbh.ch/ora/concepts/consistent_read.html)
A somewhat bit more technical details can be found at http://dioncho.wordpress.com/2009/04/18/simple-and-stupid-test-on-consistent-read/
However, what's Consistent Read got to do with MERGE statement that this article is about?
MERGE statement allows a developer to take a source result set (result of a query) and MERGE that with a target result set. Syntax is documented at http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9016.htm.
Key points from the doumantation (above) are

1. Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view.
2. This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.
3. MERGE is a deterministic statement. That is, you cannot update the same row of the target table multiple times in the same MERGE statement.

One of our developer developed a very complex MERGE statement. The program worked fine for most of the time, however at seeming random instances (for spcific input) the statement failed with Unique Key constraint Violation
It took a deeper understanding of consistent read to not only resolve the problem, but also leverage MERGE statement better in future. I hope our experience help others who are learning about Oracle.
I'll explain this experience with simplified example.
Version

 

1	select * from v$version where rownum < 2;

2

3	Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Setup
We'll set up two small tables src (source) and tgt (target with PK).

 

01	drop table tgt;

02

03	drop table src;

04

05	create table tgt as

06	select 1 c, 'A' str from dual ;

07

08	alter table tgt add primary key (c);

09

10	create table src as

11	select 1 c, 'B' str from dual union

12	select 2 c, 'B' str from dual union

13	select 2 c, 'C' str from dual

14

;

Note: The tgt has a primary key and row with value 1 already exists.
If the following MERGE state is executed, what will be the result?

 

1	merge into tgt

2

using src

3	on (tgt.c = src.c)

4	when matched then

5	update set tgt.str = src.str

6	when not matched then

7	insert (tgt.c,tgt.str)

8	values (src.c, src.str)

9

;

This will result in ERROR: SQL Error: ORA-00001: unique constraint (HR.SYS_C0021834) violated.
Developer was very flustered with this error and called it a bug in Oracle. I am not sure if other databases behave this way.
The expectation was based "row-by-row" thought process.
It was expected that when a matching row is encountered the row will be updated, but when "first non-matching row" is encountered it will be treated as an INSERT and subsequent rows with same key will be treated as UPDATE.
This was misplaced expectation.
Oracle ALWAY performs in read-consistent manner.For Merge statement both the source and target result sets are determined at the time when execution of the statement begins.
Therefore, in the example above both rows from source (src) with key value of 2 are treated as INSERT. Not the first one as INSERT and second one as UPDATE.
With this understanding, resolution of the problem is simple, but with misplaced expectation the struggle continues.
Hope this will save others from some undue struggle.