PHP/Oracle, returning inserted id's from Merge Into

Question

I am currently working on a PHP project with an Oracle database. To update a table, the php code I'm working with uses a SQL "MERGE INTO" method to loop through a table and see if values for multiple records exist in another table. If they don't exist yet, the values are inserted into my table. If the values already exist, nothing happens.

I would like to have another query run after this that uses the auto incremented id's created in the MERGE INTO query. Is there a way to get an array of the newly created ids? I was hoping for something like mysql_insert_id, but I haven't found anything like that yet.

Thanks!

Answer 1

What auto_incremented ids? AFAIK, There is no such thing in Oracle. You can simulate the behaviour by adding a trigger on the table and a sequence number but there is certainly no equivalent of mysql_insert_id().

I think you need to go back and find another way to identify your records.

C.

Answer 2

Oracle has supported the MERGE syntax since 9i. Haven't tried, but you might be able to use the RETURNING clause on the MERGE statement...

Oracle uses sequences for handling automatically incremented values. Once you've created a sequence, you can use:

sequence_name.CURVAL

..to get the current value, like what mysql_insert_id would return. To populate a primary key, you'd use:

sequence_name.NEXTVAL

To populate a primary in an INSERT statement, you'd use:

INSERT INTO your_table
  (pk_id, ..
VALUES
  (your_sequence.NEXTVAL, ...)

You can use triggers as an alternative, but they won't return the current value.