Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test
  (c1 varchar2(10),
   c2 varchar2(10),
   c3 varchar2(10));
-- changed from example so merge gets stable set of rows
insert into test(c1, c2) values ('a', 'B');
1 rows affected

insert into test(c1, c2) values ('b', 'A');
1 rows affected

commit;
select * from test;


C1
C2
C3


a
B
null

b
A
null


-- original block
begin
with function to_upper(val varchar2) return varchar is
begin
    return upper(val);
end;
merge into test a
    using (select * from test) b
on (upper(a.c1) = upper(b.c2))
when matched then 
    update set a.c3 = upper(a.c1);
end;
ORA-06550: line 3, column 15:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 7, column 1:
PLS-00103: Encountered the symbol "MERGE" 

-- original statement without PL/SQL wrapper
with function to_upper(val varchar2) return varchar is
begin
    return upper(val);
end;
merge into test a
    using (select * from test) b
on (upper(a.c1) = upper(b.c2))
when matched then 
    update set a.c3 = upper(a.c1);
ORA-00905: missing keyword

-- with added hint - still doesn't work with WITH first
with function to_upper(val varchar2) return varchar is
begin
    return upper(val);
end;
merge /*+ WITH_PLSQL */ into test a
using (select * from test) b
on (upper(a.c1) = upper(b.c2))
when matched then 
    update set a.c3 = upper(a.c1);
ORA-00905: missing keyword

-- function moved to USING clause, without hint - still fails
merge into test a
    using (
        with function to_upper(val varchar2) return varchar is
        begin
            return upper(val);
        end;
        select to_upper(c2) as c2 from test
    ) b
on (upper(a.c1) = upper(b.c2))
when matched then 
    update set a.c3 = upper(a.c1);
ORA-32034: unsupported use of WITH clause

-- function moved to USING clause, without hint, but not called - OK
merge into test a
    using (
        with function to_upper(val varchar2) return varchar is
        begin
            return upper(val);
        end;
        -- not using function...
        select upper(c2) as c2 from test
    ) b
on (upper(a.c1) = b.c2)
when matched then 
    update set a.c3 = upper(a.c1);
2 rows affected

select * from test;


C1
C2
C3


a
B
A

b
A
B


rollback;
-- function moved to USING clause, with hint - OK
merge /*+ WITH_PLSQL */ into test a
    using (
        with function to_upper(val varchar2) return varchar is
        begin
            return upper(val);
        end;
        select to_upper(c2) as c2 from test
    ) b
on (upper(a.c1) = b.c2)
when matched then 
    update set a.c3 = upper(a.c1);
2 rows affected

select * from test;


C1
C2
C3


a
B
A

b
A
B


rollback;
-- using WITH function outside USING - fails
merge /*+ WITH_PLSQL */ into test a
    using (
        with function to_upper(val varchar2) return varchar is
        begin
            return upper(val);
        end;
        select to_upper(c2) as c2 from test
    ) b
on (upper(a.c1) = to_upper(b.c2))
when matched then 
    update set a.c3 = upper(a.c1);
ORA-00904: "TO_UPPER": invalid identifier

-- using WITH function outside USING - fails
merge /*+ WITH_PLSQL */ into test a
    using (
        with function to_upper(val varchar2) return varchar is
        begin
            return upper(val);
        end;
        select to_upper(c2) as c2 from test
    ) b
on (upper(a.c1) = b.c2)
when matched then 
    update set a.c3 = to_upper(a.c1);
ORA-00904: "TO_UPPER": invalid identifier

-- function moved to both INTO and USING clauses, with hint - OK
merge /*+ WITH_PLSQL */ into (
        with function to_upper(val varchar2) return varchar is
        begin
            return upper(val);
        end;
        select to_upper(c1) as c1, c3 from test
    ) a
    using (
        with function to_upper(val varchar2) return varchar is
        begin
            return upper(val);
        end;
        select to_upper(c2) as c2 from test
    ) b
on (a.c1 = b.c2)
when matched then 
    update set a.c3 = a.c1;
2 rows affected

select * from test;


C1
C2
C3


a
B
A

b
A
B
C1	C2	C3
a	B	null
b	A	null