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.