How to return primary key from oracle merge query

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.

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 CaptureIdAS   FUNCTION SaveId(newId IN NUMBER) RETURN NUMBER;   FUNCTION GetId RETURN NUMBER;END;CREATE OR REPLACE PACKAGE BODY CaptureIdAS   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;