How to return primary key from oracle merge query

Question

I want to return the primary key from an oracle merge query. I'm using a single statement to insert if not exist and I don't want to use procedure or function to do so..

this is the sample query

merge into myTable e
  using (select :empname name from dual) s
  on (UPPER(TRIM(e.empname)) = UPPER(TRIM(s.name)))
  when not matched then insert (empname) 
    values (s.name)

and I need to get another primary key field of the myTable. the primary key is inserted using sequence and trigger

I tried adding RETURNING empID into :empId but it gives error

Cumulate

It "gives an error" because there is no facility to use returning into. However, if you want to return the PK by definition you have to return it into something, which means you're using a wrapper of some description. What is wrong with this wrapper being a PL/SQL block. If you do not want it to be a procedure/function you are going to have to specify what you do want to use otherwise it's impossible to answer this question well. – Guerrilla

I am using a single oracle query to do so. the :empId I'm passing to this query as output parameter. Is it possible to get the output parameter value from a single merge query? – Cumulate

You might be interested to read this - oracle-developer.net/display.php?id=413 – Melodymeloid

Answer 1

There's a problem.

1. Merge Into doesn't support Returning Into, so that won't work.
2. The sequence will not always be used, because it's only used when inserting new records.
3. Getting the existing value of a sequence won't work, because you get an error if you want to query Sequence.currval when the sequence wasn't used in the current session yet.

To solve it:

1. Use a procedure or anonymous program block to try to update the value. If sql%rowcount return 0 after the update, perform the insert instead.
2. Use the selection (query for UPPER(TRIM(name))) to find the record that was updated.

Michaelemichaelina

Answer 2

You can try this. You will need to declare a package to capture your id, otherwise it will not be visible to the SQL statement and you will get a error:

pls-00231: function name may not be used in SQL

So, first create the package with functions to capture and then later access the ID from the merge statement:

CREATE OR REPLACE PACKAGE CaptureId
AS
   FUNCTION SaveId(newId IN NUMBER) RETURN NUMBER;
   FUNCTION GetId RETURN NUMBER;
END;

CREATE OR REPLACE PACKAGE BODY CaptureId
AS
   capturedId NUMBER(10);

   FUNCTION SaveId(newId IN NUMBER) RETURN NUMBER IS
   BEGIN
      capturedId := newId;
      RETURN capturedId;
   END;

   FUNCTION GetId RETURN NUMBER IS
   BEGIN
      RETURN capturedId;
   END;
END;

Given a simple table and sequence generator defined as:

CREATE TABLE EMPLOYEE
(
    EMPLOYEE_ID NUMBER(10) NOT NULL,
    FIRST_NAME VARCHAR2(120) NOT NULL,
    LAST_NAME VARCHAR2(120) NOT NULL,
    CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEE_ID) ENABLE
);

CREATE SEQUENCE SEQ_EMPLOYEE;

You can then use the package in an anonymous block with your merge statement to capture the id and return it. Note that this is a very simple example and it will not work with array-bound variables unless you rework the package to capture the IDs into a table type. If I get a chance, I may try to put together an example that shows this.

BEGIN
   MERGE INTO EMPLOYEE USING (SELECT CaptureId.SaveId(:myInputId) AS EMPLOYEE_ID,
                                     :myFirstName as FIRST_NAME,
                                     :myLastName as LAST_NAME
                                FROM DUAL) B
      ON (A.EMPLOYEE_ID = B.EMPLOYEE_ID)
   WHEN NOT MATCHED THEN
       INSERT (EMPLOYEE_ID,
               FIRST_NAME,
               LAST_NAME)
       VALUES (CaptureId.SaveId(SEQ_EMPLOYEE.NEXTVAL),
               B.FIRST_NAME,
               B.LAST_NAME)
   WHEN MATCHED THEN
     UPDATE SET A.FIRST_NAME= B.FIRST_NAME,
                A.LAST_NAME= B.LAST_NAME;

   SELECT CaptureId.GetId INTO :myOutputId FROM DUAL;
END;

Photogenic

There's another problem with this approach. If you use a qualifier other than the EMPLOYEE_ID in the ON statement (e.g. A.EMPLOYEE_ID = B.EMPLOYEE_ID OR A.LAST_NAME = B.LAST_NAME) then you will fail to capture the ID. With that qualifier, you can pass a NULL for the EMPLOYEE_ID and match on a LAST_NAME. If it is not found by EMPLOYEE_ID, it will fail to capture the EMPLOYEE_ID matched by LAST_NAME. – Photogenic