Vanessa Vanessa - 3 months ago 31
Scala Question

Slick/Oracle PLAIN SQL Get auto generated ID after insert

I am trying to run a query via Slick in Scala that will insert a record into an Oracle db and return the auto generated ID as the result. I see that this is possible using Slick's syntax but is this possible using a plain SQL query? With below code I can only get a return value of -1.

val name = "Bob"
db.run(sql"""DECLARE NEW_PERSON_ID;
BEGIN
INSERT INTO TB_PEOPLE (ID, NAME)
VALUES(SEQ_PEOPLE.NEXTVAL, $name)
RETURNING ID INTO NEW_PERSON_ID;
END;""".as[Int])

Answer

It seems that Slick doesn't support output parameters so using Oracle's returning syntax won't work. A workaround I found is to first generate an ID and then insert using that ID (requires 2 queries). The queries are wrapped in a transaction.

val name = "Bob"
val action = for {
    newPersonId <- sql"""SELECT SEQ_PEOPLE.NEXTVAL FROM DUAL""".as[Int]
    _ <- sqlu"""INSERT INTO TB_PEOPLE (ID, NAME) VALUES ($newPersonId, $name)"""
}
db.run(action.transactionally)