CODEWITHSUNDEEP
sqlsql-serveroracle-databaseplsql
Adam Nowak
Adam Nowak

Reputation: 77

Equivalent merge output in oracle

I am learning oracle sql and pl/sql and I wonder if there is equivalent clause 'merge=output' in Oracle or easy and effective way to have the same result.

Link to good explonation of sql server 'merge,output' stetment. link

--sql server merge
    MERGE  #c_new AS trg
     USING #c_base AS src
        ON trg.Num_id = src.Num_id
    WHEN MATCHED )
    THEN
       UPDATE SET
          trg.Name = src.Name
         ,trg.Name_type = src.Name_type
    WHEN NOT MATCHED BY trg
    THEN
       INSERT (Num_id, Name, Name_type)
       VALUES (Num_id, src.Name, src.Name_type)
    WHEN NOT MATCHED BY src THEN DELETE
    ------------output clause from here---------------
     OUTPUT
       $ACTION,
       inserted.Name   NewName,
       deleted.Name    PrevName,
       inserted.Name_type  NewName_type,
       deleted.Name_type  PrevName_type

Return (like 'query')

$action  NewName  PrevName  NewName_type  PrevName_type
INSERT   Abc1               Tp1
DELETE            Abc2                    Tp2
UPDATE   Abc4     Abc3      Tp4           Tp3

Is it equivalent clause 'merge=output' in Oracle or easy and effective way to have the same result

Upvotes: 1

Views: 3288

Answers (1)

piers7
piers7

Reputation: 4414

The closest Oracle equivalent I know of is the RETURNING clause, but apparently not supported for MERGE statements:

"Yes, returning into is not supported for merge [...] If you want this functionality, submit an enhancement request"

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9528727800346317168

The current doco does reference being able to log the errors, but that's clearly not quite what you (or I) are after :-(

The error_logging_clause lets you capture DML errors and the log column values of the affected rows and save them in an error logging table.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/MERGE.html#GUID-5692CCB7-24D9-4C0E-81A7-A22436DC968F

Upvotes: 1

Related Questions