Oracle Error – Reporting an Error in Merge’s When-Not-Matched-Then Clause

oracle

Is it possible to report an error (raise exception or anything) in merge's when-not-matched-then clause? I'm aiming for something like

WHEN NOT MATCHED THEN RAISE NO_DATA_FOUND;

Or anything that will tell the caller that script failed.

Best Answer

You can do this using a trigger.

Note: This is HORRIBLE, but I can't think of another way of achieving this.

Test tables:

create table testmerge
(
col1 number primary key ,
col2 number
);

insert into testmerge values ( 1, 1 );

create table testmerge2
(
col1 number primary key,
col2 number 
);

insert into testmerge2 values ( 2, 2 );

Trigger on merge table:

create or replace trigger testmergeerror_btrig
  before insert on testmerge
  for each row
begin
if :new.col1 = -99999999999 and :new.col2 = -99999999999 
then
    raise_application_error(-20000,'Your error message');
end if;
end;
/

Merge:

MERGE INTO testmerge t1
    USING testmerge2 t2
    ON (t1.col1 = t2.col1)
  WHEN MATCHED THEN
    UPDATE SET t1.col2 = t2.col2
WHEN NOT MATCHED THEN
insert(col1,col2) values (-99999999999,-99999999999);

Basically, when a row doesn't match, we set some values (that your application will never use, yes I know it is a horrible way of doing it!) in the INSERT that the trigger looks for, then fires an error.

Test case:

SQL> MERGE INTO testmerge t1
  2  USING testmerge2 t2
  3  ON (t1.col1 = t2.col1)
  4  WHEN MATCHED THEN
  5  UPDATE SET t1.col2 = t2.col2
  6  WHEN NOT MATCHED THEN
  7  insert(col1,col2) values (-99999999999,-99999999999);
USING testmerge2 t2
      *
ERROR at line 2:
ORA-20000: Your error message
ORA-06512: at "PHIL.TESTMERGEERROR_BTRIG", line 4
ORA-04088: error during execution of trigger 'PHIL.TESTMERGEERROR_BTRIG'


SQL>
Related Question