2

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])
Vanessa
  • 361
  • 2
  • 3
  • 9
  • I think it's more an issue from your Oracle syntax, please check answer from Atilla [here](http://stackoverflow.com/questions/34811283/retrieve-oracle-last-inserted-identity) – Bla... Aug 20 '16 at 08:46
  • 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 Aug 22 '16 at 10:09

1 Answers1

0

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)
Vanessa
  • 361
  • 2
  • 3
  • 9