Oracle ORA-00942 - Lowercase identifiers

[daniel...@gmail.com]

We are experiencing problems with the slick extensions for Oracle.

We are working with an existing DB schema which has all its identifiers (table names, columns) in lowercase characters.

(We'd like to leave it like that due to compatibility with an existing application)

 2014-09-29 11:03:23,662 DEBUG [lt-dispatcher-6] s.s.jdbc.JdbcBackend.statement - Preparing insert statement: INSERT INTO "user" ("network_id","firstname","lastname","gender","language_id","email","unique_id","country_id","image","location","about","is_activated","is_deleted","is_hidden","is_blocked","date_deleted","last_seen","last_update","created") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?), returning: id
scripts 2014-09-29 11:03:23,689 ERROR [lt-dispatcher-6] akka.actor.OneForOneStrategy   - ORA-00942: table or view does not exist
scripts
scripts java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

The Slick Documentation states 

"Note: The Oracle JDBC driver has problems with quoted identifiers. Columns which are returned from inserts must not require quoted names (in particular, they must not contain lower-case characters or be equal to a reserved word), otherwise a bug in the Oracle JDBC driver triggers an ORA-00904 error."

http://slick.typesafe.com/doc/2.1.0/extensions-api/#com.typesafe.slick.driver.oracle.OracleDriver

Is this something that will be fixed or is there a way to work around?

Best regards

Daniel

[Stefan Zeiger]

I have been unable to find a workaround for this problem when I implemented the new functionality in OracleDriver (in particular, feeding the JDBC driver quoted names does not work, either). This is something that would have to be fixed by Oracle in their JDBC driver or even in the database server.

--
Stefan Zeiger
Slick Tech Lead
Typesafe - Build Reactive Apps!
Twitter: @StefanZeiger

[daniel...@gmail.com]

We now helped ourselves by switching to a 

val id = Q.queryNA[Int](s"""SELECT "${sequenceName}".CURRVAL FROM DUAL""").first

construct when using the oracle driver. 

[Stefan Zeiger]

On 2014-09-30 20:10, daniel...@gmail.com wrote:

We now helped ourselves by switching to a 

val id = Q.queryNA[Int](s"""SELECT "${sequenceName}".CURRVAL FROM DUAL""").first

construct when using the oracle driver.

We have an API for sequences in the Lifted Embedding, so you don't have to drop down to SQL for this.

Using this fallback internally in OracleDriver in the future could be an option, but the problem is that it's not guaranteed to work if the AutoInc column was not created by Slick (in which case the sequence name wouldn't have to match the one Slick would generate) or if you manually added a trigger that generates more sequence values (thus modifying CURRVAL before we have a chance to read it).