HiveBrain v1.2.0
Get Started
← Back to all entries
debugMinor

Report an error in merge's when-not-matched-then clause

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
errormatchedmergereportthenwhennotclause

Problem

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.

Solution

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>

Code Snippets

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 );
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 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);
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>

Context

StackExchange Database Administrators Q#173406, answer score: 7

Revisions (0)

No revisions yet.