2017/01/18

MERGE statement can fail with ORA-00001: unique constraint violated

How can MERGE statement fail with ORA-00001: unique constraint violated if all other things have been done right? I mean both source and target tables have primary key constraints and all columns from that constraints are mentioned in ON clause.
There is a tiny window of possibility. Suppose the first session inserts a row into source table with commit, then the second session starts long-running MERGE into the target table. While it is running, the first session inserts the same row into target table and commits it. When the second session reaches that new row, it gets ORA-00001.
When the second session started MERGE statement, the first thing it did was that it outer-joined the source and target tables. At that moment questioned row existed in source table, but did not exist in target table (or invisible due to uncommited transaction), so, MERGE decided to put the row into insert branch of MERGE statement.

Here is a test-case for reproducing this effect.

First session:
--preparation
drop table t1 purge;
drop table t2 purge;

create table t1 (
 OBJECT_ID     NUMBER,
 OBJECT_NAME   VARCHAR2(128),
 OBJECT_TYPE   VARCHAR2(23),
 filler        char(1000) default 'X'
 );

insert into t1 select OBJECT_ID,OBJECT_NAME,OBJECT_TYPE, 'Y' from all_objects where rownum < 5000;
create table t2 as select * from t1 where 1=2;
alter table t1 add constraint t1_pk primary key (object_id);
alter table t2 add constraint t2_pk primary key (object_id);

--start of the experiment
insert into t1 (object_id, object_name, object_type) values (100000,'test_obj', 'test_type');
commit;
insert into t2 (object_id, object_name, object_type) values (100000,'test_obj', 'test_type');

Second session:
merge into t2 trg
 using t1 src
 on (trg.object_id=src.object_id)
 when matched then update set
 trg.object_name=src.object_name
 when not matched then insert
 (trg.object_id,trg.object_name, trg.object_type)
 values

 (src.object_id,src.object_name,src.object_type);

First session:
commit;

Second session:
merge into t2 trg
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.T2_PK) violated

2017/01/12

Swallowing exceptions

Have you heard about Oracle can hide some exceptions raised from user code?
One well-known situation is described here.

But there is one more case I've just found.
Try this code:

create or replace procedure test1 is
begin
  raise no_data_found;
end;
/
begin test1; end;
/
begin
      dbms_scheduler.create_job(job_name        => 'T1',
                                job_type        => 'STORED_PROCEDURE',
                                job_action      => 'TEST1',
                                enabled         => TRUE,
                                start_date      => systimestamp,
                                auto_drop       => true);
END;
/
select ACTUAL_START_DATE, LOG_DATE,STATUS,ERRORS from user_scheduler_job_run_details where job_name='T1';

The final query shows STATUS='SUCCEEDED' even though call of test1() raises a no_data_found exception.

Btw, if job is defined slightly differently

  job_type        => 'PLSQL_BLOCK',
  job_action      => 'BEGIN raise no_data_found; end;',

all works as expected, i.e. STATUS='FAILED'.

Checked on 11.2.0.3/4 and 12.1.0.2.

UPD, see MOS doc 1331778.1 for more details.