Search code examples
phporacle-databasepdolast-insert-id

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

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.

Solution

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