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.

Faeroese

Have you tried fetching it through a query instead? (Assuming Oracle has a function for that) – Argentous

Answer 1

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));

Eltonelucidate

Is this race condition safe though? (I have no clue about Oracle, just curious) – Argentous

@Argentous - 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. – Eltonelucidate

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 functions, usable inline? (Asking out of curiosity.) – Paronychia

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 – Eltonelucidate

Answer 2

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);

Guttering