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

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.

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;