0

We have a database setup where we have a separate user for owners and users of database tables in an Oracle database. This means that in practice each query is prefixed like this: ownername.tablename

This works just fine if I just write the whole thing statically in Slick's SQLInterpolation.sql function:

(sql"select foo_owner.foo_sequence.nextval from dual").as[Long].first()

The problem is, that the owner prefix changes depending on test/prod. environment. What I'd like to do is this:

(sql"select $owner.foo_sequence.nextval from dual").as[Long].first()

But SQL interpolation doesn't work with it. I get this error Oracle: 

An exception or error caused a run to abort: ORA-22806: not an object or REF

Any suggestions? I can of course fall back to the more verbose StaticQuery, but using sql/sqlu interpolation would be much more compact.

auramo
  • 13,167
  • 13
  • 66
  • 88
  • Note that there is a high cost if you use Slick with Oracle in production. See the slick-extensions license. – JasonG Jul 10 '13 at 20:23

2 Answers2

3

Using $foo inserts foo as a bind variable. You need to prefix it with # to insert a literal:

(sql"select #$owner.foo_sequence.nextval from dual").as[Long].first()
szeiger
  • 1,406
  • 9
  • 11
0

Is removing prefix acceptable? In your code you can run
ALTER SESSION SET CURRENT_SCHEMA=yourOwner
This way you do not need to prefix SQL. Just be sure that you do not have CREATE TABLE or any other DML as it will try to run it on owner's schema.

igr
  • 3,409
  • 1
  • 20
  • 25