For a long time, we all “knew” that MERGE did not support a RETURNING clause. You can ask ChatGPT, Gemini, or Google, and the answer would still be the same: Nope, no luck! LLMs respond with confidence, and Google’s top results show various workarounds for the problem. I swear I saw the lifting of this restriction in the New Features Guide back when the version was still called 23c :-). Since I’m currently preparing a conference talk where this is relevant, I naturally wanted to check whether my memory was just playing tricks on me — or if it’s actually true.
TL;DR:
Yes, Oracle 23ai now supports MERGE … RETURNING. ChatGPT and Gemini still deny it, even inventing fake error messages. But the Oracle 23ai documentation quietly confirms it — and real tests prove it works, including OLD and NEW value support.
A Word of Caution
In today’s fast-paced world, everyone wants to save time and optimize effort. As an Oracle developer, why dig through AskTom, Oracle Docs, or StackOverflow when ChatGPT gives you a confident answer, complete with examples?
Having used LLMs a lot in recent weeks and months, I’m impressed by their progress. But despite improvements, hallucinations are still very much a thing — and some are tricky to spot. You need to know your stuff.
Both ChatGPT and Google Gemini insist that MERGE with RETURNING is impossible. ChatGPT even invents a fictitious error message — ORA-38911 — which does exist, but has nothing to do with MERGE or RETURNING.
Figure 1: ChatGPT with fictitious error message
Figure 2: Also Google’s LLM doesn’t know about new feature
I’m not saying don’t use LLMs — just always double-check the answers.
A Hard-to-Find Feature
Next stop: Oracle documentation. And yes, you really have to look twice. In the New Features Guide, the section is called SQL UPDATE RETURN clause enhancements, and MERGE is just mentioned casually — as if it had always supported RETURNING.
Figure 3: MERGE casually dropped in under “new/old values” enhancements
Looking at the MERGE documentation page, you’ll find the RETURNING clause shown in the syntax railroad diagram — but not a single word about it in the actual text!
Curious, I wondered whether I had missed this feature already being added in Oracle 21c? A quick check confirms: nope. Back then, RETURNING was only documented for INSERT, UPDATE AND DELETE. Digging deeper into the 23ai docs, the same section from “Database PL/SQL Language Reference” documents the usage with MERGE!
Test
Let’s try it out to be sure. Listing 1 shows that the RETURNING clause works with MERGE, and that the 23ai enhancements like OLD and NEW values are fully functional.
SQL> SELECT * FROM emp_sal_increase
DEPTNO INCREASE_PCT
---------- ------------
20 20
1 row selected.
SQL> DECLARE
TYPE t_sal_tab IS TABLE OF emp.sal%TYPE;
TYPE t_empno_tab IS TABLE OF emp.empno%TYPE;
l_empno t_empno_tab;
l_salo t_sal_tab;
l_saln t_sal_tab;
BEGIN
MERGE INTO emp t
USING emp_sal_increase q
ON (t.deptno = q.deptno)
WHEN MATCHED THEN UPDATE SET t.sal=t.sal*(1+q.increase_pct/100)
RETURNING empno, OLD sal, NEW sal BULK COLLECT INTO l_empno, l_salo, l_saln;
FOR i IN l_salo.first .. l_salo.last LOOP
DBMS_OUTPUT.put_line('EMPNO=' || l_empno(i)|| ', SAL changed from '||l_salo(i) ||' to ' ||l_saln(i));
END LOOP;
END;
PL/SQL procedure successfully completed.
EMPNO=7369, SAL changed from 800 to 960
EMPNO=7566, SAL changed from 2975 to 3570
EMPNO=7788, SAL changed from 3000 to 3600
EMPNO=7876, SAL changed from 1100 to 1320
EMPNO=7902, SAL changed from 3000 to 3600
Listing 1: Returning clause works for MERGE in 23ai
Listing 1 shows BULK COLLECT for multiple rows, but it works with scalar values too — even aggregate functions, as shown in Listing 2.
SQL> DECLARE
v_old_avg NUMBER;
v_new_avg NUMBER;
BEGIN
MERGE INTO emp t
USING emp_sal_increase q
ON (t.deptno = q.deptno)
WHEN MATCHED THEN UPDATE SET t.sal=t.sal*(1+q.increase_pct/100)
RETURNING AVG(old sal), AVG(new sal) INTO v_old_avg, v_new_avg;
DBMS_OUTPUT.put_line('Average SAL changed from '||v_old_avg ||' to ' ||v_new_avg);
END;
PL/SQL procedure successfully completed.
Average SAL changed from 2175 to 2610
Listing 2: Returning with scalar values
Conclusion
So yes, MERGE … RETURNING now works in Oracle 23ai — including with OLD and NEW values. It’s real, it’s documented (somewhere), and can be quite useful.
But Oracle, if you’re listening: could we maybe highlight this a bit more clearly in the documentation?
This was a long-awaited feature — many developers have been hoping for it for years. No need to hide it in a syntax diagram. Give it the spotlight it deserves!