Oracle ORA-00942 - Lowercase identifiers

198 views
Skip to first unread message

daniel...@gmail.com

unread,
Sep 29, 2014, 5:05:55 AM9/29/14
to scala...@googlegroups.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."


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

Best regards

Daniel

Stefan Zeiger

unread,
Sep 30, 2014, 4:55:06 AM9/30/14
to scala...@googlegroups.com
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

unread,
Sep 30, 2014, 2:10:54 PM9/30/14
to scala...@googlegroups.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

unread,
Oct 1, 2014, 8:02:53 AM10/1/14
to scala...@googlegroups.com
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).
Reply all
Reply to author
Forward
0 new messages