Retrieve auto-generated ID from Oracle database

[mchajii]

I'm using Oracle database and I'm facing an issue when trying to retrieve the last generated ID via an insert statement.
Oracle provides the possibility to retrieve the auto-generated keys using the getGeneratedKeys() method.

I built the below query following the doobie documentation:

          sql"insert into person (name, age) values ($name, $age)"
            .update
            .withUniqueGeneratedKeys[Long]("id")

Unfortunately, it doesn't work. I'm getting the following error: java.sql.SQLSyntaxErrorException: ORA-00931: missing identifier

However, if I rewrite the query as follows it works fine:

        val sql = "insert into person (name, age) values ($name, $age)"
        Update[(String, Int)](sql)
          .withUniqueGeneratedKeys[Long]("id")(("Bob", 12))

I don't understand why the first query is failing. Is there any difference between the 2 queries ?

doobie version used: 0.12.1