Thanks for the question, John.

Asked: May 05, 2016 - 12:23 pm UTC

Last updated: February 04, 2022 - 10:54 am UTC

Version: 11.2.0.4

Viewed 50K+ times! This question is

You Asked

Hi,
I've always been a great fan of the MERGE statement, and find it great for writing basic insert/update/delete table handlers. However recently I was very disappointed to learn that the RETURNING clause is not supported on the INSERT. I.e.

Merge Into xxTmp1
Using (Select Null, 'Test' val From dual) n
On (xxTmp1.id = newrow.id)
When Matched Then Update Set (val=n.val)
Delete Where (pDeleteFlag='Y')
When Not Matched Then Insert (id, val) Values (xxTmp_Seq.NextVal, n.val)
Returning id Into pID;

Doesn't work. The purpose of the above would be to have a single procedure "maintain vals" which if a valid ID is passed in then the appropriate row is updated (or if the delete flag is specified then it's deleted), otherwise a new row is inserted with an ID from a sequence.
However using MERGE it seems impossible for me to retrieve the ID of the row that was inserted.

I can't write the row using a merge and then query back both for performance reasons, and also because there's absolutely no guarantee that the last row is actually mine.

Is this by design (a technical reason why the functionality has been omitted) or a bug turned into a feature?

Now I know there are some workarounds like coding the update first, checking Sql%RowCount and doing the insert (with returning) if that returns zero, however this is both more code and isn't as "nice" an approach in my eyes (I appreciate that there may be no performance differences as the whole merge is non-atomic). Is there any other way you'd recommend approaching such a requirement? Are there any neat tricks or workarounds to achieve this in a concise manner that the merge provides?

Many thanks,
John

and Chris said...

Yes, returning into is not supported for merge. From the docs:

"The static RETURNING INTO clause belongs to a DELETE, INSERT, or UPDATE statement. The dynamic RETURNING INTO clause belongs to the EXECUTE IMMEDIATE statement."

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/returninginto_clause.htm#LNPLS01354

If you want this functionality, submit an enhancement request.

You could investigate modifying Adrian Billington's etl package for decomposing the sql%rowcount for merge:

http://www.oracle-developer.net/display.php?id=220

Whether this performs better than splitting the merge into an update+insert is something you'll need to test in your environment.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

John Keymer, May 05, 2016 - 1:05 pm UTC

Thank-you - I understand it's not supported, I am interested in your views on what is the best way to achieve this functionality for such a requirement - would you recommend using an update, check rowcount and do the insert if necessary?

Thanks

May 05, 2016 - 3:02 pm UTC

It depends :)

If you're mostly updating and inserting rarely, then yes doing the update and checking the rowcount is a good approach.

If you expect to be mostly inserting then it may be better to try a different method.

It comes down to knowing what your performance requirements are and implementing the simplest, most obvious approach that meets these.

John Keymer, May 06, 2016 - 8:48 am UTC

Thanks, the system I'm using is quite low volume and the table handlers are accessed from a GUI so there's only a very few records going through. On that basis the additional overhead of doing a potential redundant update step is pretty negligible in the grand scheme of things and so will suffice in this case.
I guess whatever approach is taken we always have to at least read the row first, whether that is a merge, an update first, or read the row and then have a conditional construct to perform an update or insert.

Kind regards
John

May 06, 2016 - 8:51 am UTC

Ype, you need the check to see if the row exists.

Given your situation I'd stick with the update then insert method.

Aggregation unsupported with Insert and Returning clause

Indy, June 09, 2020 - 7:27 pm UTC

Dear TOM,

Am i missing something obvious? Tried the below in 19c.

create table test(a number, b varchar2(10));

SQL> ed
Wrote file afiedt.buf

1 declare
2 i number;
3 begin
4 insert into test(a,b)
5 select 1, 'a' from dual union all
6 select 2, 'b' from dual union all
7 select 3, 'c' from dual
8 returning max(a) into i
9 ;
10 dbms_output.put_line('i = ' || i);
11* end;
SQL> /
returning max(a) into i
*
ERROR at line 8:
ORA-06550: line 8, column 11:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

June 10, 2020 - 1:25 am UTC

I don't think we've ever supported that. It is only supported for updates and deletes


SQL> create table test(a number, b varchar2(10));

Table created.

SQL>
SQL> insert into test values (1,'a');

1 row created.

SQL> insert into test values (2,'b');

1 row created.

SQL> insert into test values (3,'c');

1 row created.

SQL> insert into test values (4,'d');

1 row created.

SQL>
SQL>
SQL> declare
  2    i number;
  3  begin
  4    insert into test(a,b)
  5    select 1, 'a' from dual union all
  6    select 2, 'b' from dual union all
  7    select 3, 'c' from dual
  8    returning max(a) into i;
  9  end;
 10  /
  returning max(a) into i;
            *
ERROR at line 8:
ORA-06550: line 8, column 13:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored


SQL>
SQL> declare
  2    i number;
  3  begin
  4    update test
  5    set b = 'x'
  6    returning max(a) into i;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL>

You can return *all* of the inserted values using a RETURNING BULK COLLECT but you can't just return an aggregate.

There must be another access path

Karsten Spang, February 04, 2022 - 9:24 am UTC

The is something fundamentally wrong about the example. You match on the id, and if not found you insert a new id from a sequence. The fact that you match on the id indicates that you already know the id, which contradicts the use of a sequence.

You will need to match on something different than the id, which probably will be an alternate key on the table. You can use that for getting the id afterwards.

Besides, assuming that the first (null) value on line 2 was supposed to have the alias

id

and that

newrow

in line 3 was supposed to be the

in line 2, you match on

null

, meaning that there will never be a match, so you might as well use an insert statement.

February 04, 2022 - 10:54 am UTC

Pretty sure the original statement is just a "for example" showing what they're trying to do. It's also possible they're loading staging data where some rows don't have IDs assigned yet.

Or perhaps newrow.id refers to the parameter ID in the function NEWROW.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks

Aggregation unsupported with Insert and Returning clause

There must be another access path

More to Explore

PL/SQL demos

PL/SQL docs

Best workaround to use RETURNING with the MERGE statement.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks

Aggregation unsupported with Insert and Returning clause

There must be another access path

More to Explore

PL/SQL demos

PL/SQL docs