Slick/Oracle PLAIN SQL Get auto generated ID after insert

Question

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

I think it's more an issue from your Oracle syntax, please check answer from Atilla here — Bla...
Part of the problem is that your declare new_person_id is missing a datatype (probably tb_people.id%type), so the block fails to compile and maybe that's giving the -1. However fixing that won't return anything to the caller (and I don't know Scala). — William Robertson

Vanessa Vanessa · Accepted Answer · 2016-08-26T06:40:46

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)

Slick/Oracle PLAIN SQL Get auto generated ID after insert

1 Answers