debugMinor
Report an error in merge's when-not-matched-then clause
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
Or anything that will tell the caller that script failed.
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:
Trigger on merge table:
Merge:
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
Test case:
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.