MERGE statement with "WITH FUNCTION" definition

Question

I am trying to use a function declared in WITH clause, into a MERGE statement. Here is my code:

create table test
  (c1 varchar2(10),
   c2 varchar2(10),
   c3 varchar2(10));



insert into test(c1, c2) values ('a', 'A');
insert into test(c1, c2) values ('b', 'A');

select * from test;

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 = to_upper(a.c1);
end; 

but I am getting this error:

Error report - ORA-06550: line 2, column 15: PL/SQL: ORA-00905: missing keyword ORA-06550: line 2, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 6, column 1: PLS-00103: Encountered the symbol "MERGE" 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Can someone explain why it is not working, please?

Thank you,

Answer 1

The with clause is part of the select syntax. As the railroad diagram for that shows, the only thing that's valid after the with clause is the select keyword - you don't have that, hence the error you are seeing.

As the documentation also says says:

The plsql_declarations clause lets you declare and define PL/SQL functions and procedures. You can then reference the PL/SQL functions in the query in which you specify this clause, as well as its subqueries, if any.

and

If the query in which you specify this clause is not a top-level SELECT statement, then the following rules apply to the top-level SQL statement that contains the query:

• If the top-level statement is a SELECT statement, then it must have either a WITH plsql_declarations clause or the WITH_PLSQL hint.
• If the top-level statement is a DELETE, MERGE, INSERT, or UPDATE statement, then it must have the WITH_PLSQL hint.

So, you can't apply the with clause to the whole merge statement, you can only use it as part of a query within that, i.e. in the using clause:

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

Without the /*+ WITH_PLSQL */ hint this would error with "ORA-32034: unsupported use of WITH clause" (though only if you actually call the function, otherwise the complier seems to strip the unused function out and not complain about it).

But the function is still only in scope for the using clause; you can't refer to it in the on or update clauses. You would need to do any function calls within the using clause; and if you need the same function applied to anything from the target table, you would need to repeat the function and call that with the into clause, e.g.:

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;

db<>fiddle showing things that do and don't work.

It's quite a contrived example so it would probably be clearer with a more real-world scenario.