{"id":2503,"date":"2025-05-07T22:12:50","date_gmt":"2025-05-07T20:12:50","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=2503"},"modified":"2025-09-18T15:10:53","modified_gmt":"2025-09-18T13:10:53","slug":"merge-and-dml-returning-clause-in-oracle-23ai","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/merge-and-dml-returning-clause-in-oracle-23ai\/","title":{"rendered":"MERGE and DML RETURNING clause in Oracle 23ai"},"content":{"rendered":"\n

For a long time, we all “knew” that MERGE <\/strong>did not support a RETURNING <\/strong>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\u2019m currently preparing a conference talk where this is relevant, I naturally wanted to check whether my memory was just playing tricks on me \u2014 or if it\u2019s actually true.<\/p>\n\n\n\n\n\n\n\n

TL;DR:<\/h2>\n\n\n\n

Yes, Oracle 23ai now supports MERGE … RETURNING<\/strong>. ChatGPT and Gemini still deny it, even inventing fake error messages. But the Oracle 23ai documentation quietly confirms it \u2014 and real tests prove it works, including OLD<\/strong> and NEW <\/strong>value support.<\/p>\n\n\n\n

A Word of Caution<\/h2>\n\n\n\n

In today\u2019s 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?<\/p>\n\n\n\n

Having used LLMs a lot in recent weeks and months, I\u2019m impressed by their progress. But despite improvements, hallucinations are still very much a thing \u2014 and some are tricky to spot. You need to know your stuff.<\/p>\n\n\n\n

Both ChatGPT and Google Gemini insist that MERGE <\/strong>with RETURNING <\/strong>is impossible. ChatGPT even invents a fictitious error message \u2014 ORA-38911<\/strong> \u2014 which does<\/em> exist, but has nothing to do with MERGE <\/strong>or RETURNING<\/strong>.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n

Figure 1: ChatGPT with fictitious error message<\/em><\/strong><\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

Figure 2: Also Google’s LLM doesn’t know about new feature<\/em><\/strong><\/p>\n\n\n\n

I\u2019m not saying don\u2019t use LLMs \u2014 just always<\/em> double-check the answers.<\/p>\n\n\n\n

A Hard-to-Find Feature<\/h2>\n\n\n\n

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<\/strong>, and MERGE <\/strong>is just mentioned casually \u2014 as if it had always supported RETURNING<\/strong>.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

Figure 3: MERGE casually dropped in under \u201cnew\/old values\u201d enhancements<\/em><\/strong><\/p>\n\n\n\n

Looking at the MERGE documentation page<\/a>, you\u2019ll find the RETURNING <\/strong>clause shown in the syntax railroad diagram \u2014 but not a single word about it in the actual text!<\/p>\n\n\n\n

Curious, I wondered whether I had missed this feature already being added in Oracle 21c? A quick check confirms: nope. Back then, RETURNING <\/strong>was only documented for INSERT, UPDATE AND DELETE<\/a>. Digging deeper into the 23ai docs, the same section from “Database PL\/SQL Language Reference” documents the usage with MERGE<\/a>!<\/p>\n\n\n\n

Test<\/h2>\n\n\n\n

Let\u2019s try it out to be sure. Listing 1 shows that the RETURNING <\/strong>clause works with MERGE<\/strong>, and that the 23ai enhancements like OLD <\/strong>and NEW <\/strong>values are fully functional.<\/p>\n\n\n

\nSQL> SELECT * FROM emp_sal_increase\n\n    DEPTNO INCREASE_PCT\n---------- ------------\n        20           20\n1 row selected.\n\nSQL> DECLARE \nTYPE t_sal_tab IS TABLE OF emp.sal%TYPE;\nTYPE t_empno_tab IS TABLE OF emp.empno%TYPE;\nl_empno t_empno_tab;\nl_salo t_sal_tab;\nl_saln t_sal_tab;\nBEGIN \n    MERGE INTO emp t\n    USING  emp_sal_increase  q\n    ON    (t.deptno = q.deptno)\n    WHEN MATCHED THEN UPDATE SET t.sal=t.sal*(1+q.increase_pct\/100)\n    RETURNING empno, OLD sal, NEW sal BULK COLLECT INTO l_empno, l_salo, l_saln;\n   \n    FOR i IN l_salo.first .. l_salo.last LOOP\n      DBMS_OUTPUT.put_line('EMPNO=' || l_empno(i)|| ', SAL changed from '||l_salo(i) ||' to ' ||l_saln(i));\n    END LOOP;\nEND;\n\nPL\/SQL procedure successfully completed.\n\nEMPNO=7369, SAL changed from 800 to 960\nEMPNO=7566, SAL changed from 2975 to 3570\nEMPNO=7788, SAL changed from 3000 to 3600\nEMPNO=7876, SAL changed from 1100 to 1320\nEMPNO=7902, SAL changed from 3000 to 3600\n<\/pre><\/div>\n\n\n
Listing 1: Returning clause works for MERGE in 23ai<\/em><\/strong><\/p>\n\n\n\n
Listing 1 shows BULK COLLECT<\/strong> for multiple rows, but it works with scalar values too \u2014 even aggregate functions, as shown in Listing 2.<\/p>\n\n\n
\nSQL> DECLARE \nv_old_avg NUMBER;\nv_new_avg NUMBER;\nBEGIN \n    MERGE INTO emp t\n    USING  emp_sal_increase  q\n    ON    (t.deptno = q.deptno)\n    WHEN MATCHED THEN UPDATE SET t.sal=t.sal*(1+q.increase_pct\/100)\n    RETURNING AVG(old sal), AVG(new sal)  INTO v_old_avg, v_new_avg;\n    \n    DBMS_OUTPUT.put_line('Average SAL changed from '||v_old_avg ||' to ' ||v_new_avg);\n    \nEND;\nPL\/SQL procedure successfully completed.\n\nAverage SAL changed from 2175 to 2610\n<\/pre><\/div>\n\n\n
Listing 2: Returning with scalar values<\/em><\/strong><\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
So yes, MERGE … RETURNING<\/strong> now works in Oracle 23ai \u2014 including with OLD <\/strong>and NEW <\/strong>values. It\u2019s real, it\u2019s documented (somewhere), and can be quite useful.<\/p>\n\n\n\n
But Oracle, if you’re listening: could we maybe highlight this a bit more clearly in the documentation?<\/strong>
This was a long-awaited feature \u2014 many developers have been hoping for it for years. No need to hide it in a syntax diagram. Give it the spotlight it deserves!<\/p>\n\n\n\n
UPDATE <\/h2>\n\n\n\n
Please check Connor McDonald’s post<\/a> about MERGE … RETURNING<\/strong> clause in 23ai and using it to get separated rows inserted<\/em><\/strong> and rows updated<\/strong><\/em> counts. Additionally, he suggests using MERGE<\/strong> instead of INSERT AS SELECT<\/strong> when a RETURNING<\/strong> clause is needed.<\/p>\n\n\n