Why doesn't PDO's Oracle driver implement lastInsertId()?

Question

I get this error in PDO:

error: Message: PDO::lastInsertId() [pdo.lastinsertid]: SQLSTATE[IM001]: Driver does not support this function: driver does not support lastInsertId()

when trying to get last inserted id from an oracle database. I added the sequence string to the last insert id function but still not working. Google doesn't say much regarding this error on Oracle with PDO.

Have you tried fetching it through a query instead? (Assuming Oracle has a function for that) — Pekka, Feb 17 '12 at 13:39

score 13 · Accepted Answer · edited Feb 17 '12 at 17:38

13

Oracle doesn't have autoincrement columns, so lastInsertId isn't supported in the same way as for MySQL. You have to implement the equivalent "by hand" using Oracle sequences.

Create an oracle sequence for every table that requires it, and use NEXTVAL to retrieve it whenever you need to do an insert, then use that value when inserting in the table.

$sh = $conn->prepare('SELECT uid_seq.NEXTVAL AS nextInsertID FROM DUAL');
$sh->execute();
$nextInsertId = $sh->fetchColumn(0);

$sh = $conn->prepare("INSERT INTO table (id, data) VALUES(?, 255)");
$sh->execute(array($nextInsertId));

edited Feb 17 '12 at 17:38

Charles

50,943
13
104
142

answered Feb 17 '12 at 13:50

Mark Baker

209,507
32
346
385

Is this race condition safe though? (I have no clue about Oracle, just curious) – Pekka Feb 17 '12 at 16:31
@Pekka - nope, sequence numbers are issued without risk of race conditions by the Oracle server, incremented and issued on every request for NEXTVAL... like MySQLs autoincrement, except at the level of the sequence rather than a table level. I think somebody would have encountered problems with them otherwise, and an issue in something so fundamental would have broken Oracle as a database. HOWEVER, there's nothing to prevent users storing "any old value" in the id column using this method. – Mark Baker Feb 17 '12 at 16:55
I find the requirement to fetch the next value in the sequence, then issue the insert to be kind of ugly. Does Oracle have anything like Postgres' [`curval` / `nextval` / `lastval`](http://www.postgresql.org/docs/current/static/functions-sequence.html) functions, usable inline? (Asking out of curiosity.) – Charles Feb 17 '12 at 17:41
3

INSERT INTO table (id, data) VALUES(uid_seq.NEXTVAL, 255); followed by SELECT uid_seq.CURRVAL AS lastInsertID FROM DUAL; would also be perfectly valid.... but LASTVAL doesn't exist – Mark Baker Feb 17 '12 at 17:52

score 0 · Answer 2 · answered Jul 13 '18 at 06:39

In Oracle, you should have created an oracle sequence for tables which requires an auto increment column.

If you want to insert, then you can do it in the same time rather than query nextIncrementId and insert as below:

$sh = $conn->prepare("INSERT INTO table (id, data) VALUES(SEQUENCE_NAME.NEXTVAL, ?)");
$sh->execute(array($valueToBeInsertedInDataColumn));

If you just want to know the last insert id, then don't use nextval because whenever you call it, it increment the value to the next one. You should use CURRVAL for that purpose. Below is an example:

$sh = $conn->prepare("SELECT SEQUENCE_NAME.CURRVAL AS lastInsertId FROM DUAL");
$lastInserId = $sh->execute();

Debug: print_r($lastInserId);

Why doesn't PDO's Oracle driver implement lastInsertId()?

2 Answers2